How to hold Database Name in a variable and use it with USE keyword

Question

Post reply

How to hold Database Name in a variable and use it with USE keyword

mr_adeelalisyed

SSC-Addicted

Points: 470
More actions
November 12, 2009 at 9:43 am

#98269

Hello experts,
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
(
[CapturedDate] ASC,
[DbName] ASC
)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
DBNameDBCreationDateCapturedDateNoOfTable
ABC10/10/200810/10/20094
DEF10/10/200810/10/200919
XYZ10/10/200810/10/200812
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.
USE @name
GO
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?
Thanks a lot in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

to get the table counts, i see you'll have to loop thru everything one way or the other;

here's one way to do what you asked...i'm using sp_msforeachdb, which uses a cursor anyway;

CREATE TABLE #DbReport(

DBName varchar(100),

DBCreationDate datetime,

CapturedDate datetime,

NoOfTables int )

sp_msForEachdb 'INSERT INTO #DbReport

SELECT

''?'',

NULL,

getdate(),

COUNT(name) AS CNT

from ?.sys.objects

WHERE type_desc = ''USER_TABLE'' '

select * from #DbReport --we got it all except the creation date

update #DbReport

set DBCreationDate = create_date

from sys.databases

WHERE DBName = name

select * from #DbReport

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mr_adeelalisyed SSC-Addicted Points: 470 More actions · Answer 2

Thanks a lot Lowell for your help. Yes you got it exactly what i wanted to do. This is such a great great example and I learn from it. Now I'll try to practice my loops to accomplish the same results. Once again Thanks,