Unanswered: How to hold Database Name in a variable and use it with USE keyword
I’m trying to write T-SQL code to pull all the database names and store information in one of my table whose structure is as follows.
CREATE TABLE [dbo].[TotalDatabase](
[DbName] [varchar](20) NOT NULL,
[DbCreationDate] [datetime] NOT NULL,
[CapturedDate] [datetime] NOT NULL,
[NoOfTable] [char](3) NOT NULL,
CONSTRAINT [XPKTotalDatabase] PRIMARY KEY NONCLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
So what I need is all databases, their creation data, captured data (which will be today’s date “getdate()”) and no of tables which every database has. I’ve pull DatabaseNames, CreationData & CapturedDate(which is today’s date) though sys.databases. Now what I need to do is count all the tables for each database. Here is the sample data of what I need
The idea which came on my mind is to create a cursor and hold DBName in a variable and use this variable in a loop to get all table information though sys.tables. Now my problem is when I tried to write code like below I’d error that vaiable is not been define.
However I’ve defined this variable. Now 1st question can I hold different database name in a variable and as above and use it? If not then how I can accomplish it?