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

Question

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

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

 

DBName          DBCreationDate           CapturedDate               NoOfTable

ABC                10/10/2008                  10/10/2009                  4

DEF                 10/10/2008                  10/10/2009                  19

XYZ                10/10/2008                  10/10/2008                  12

 

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 1^st 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.

Answer 1

Hi CRM

You could use the cursor to reference the DBName differently however (if you run it using sp_executesql inside your cursor)

So you could select count(*) from <dbname>.sys.objects where type_desc = 'user_table' and avoid the use of the "USE dbname" construct.

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 2

you can use what Evans tip
but if you really want to use the USE @database then you can switch to SQLCMD mode with :CONNECT @VARIABLE.

---

Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

Answer 3

I am using a temp table in the example, but you could do something like this.

CREATE TABLE #TotalDatabase( 
      [DbName] [varchar](50) NOT NULL, 
      [DbCreationDate] [datetime] NOT NULL, 
      [CapturedDate] [datetime] NOT NULL, 
      [NoOfTable] [varchar](10) NOT NULL);


EXEC sp_msforeachdb'
USE ?

INSERT INTO #TotalDatabase
SELECT  ''?'' AS DBName,
       GETDATE() CapturedDate, 
       create_date AS DBCreationDate, 
      (SELECT COUNT(*) FROM sys.tables) NoOfTable
FROM sys.databases
WHERE name = ''?''
'

SELECT * FROM #TotalDatabase

DROP TABLE #TotalDatabase

---

Ken Simmons
http://cybersql.blogspot.com
http://twitter.com/kensimmons

Answer 4

As mentioned in the post above you can use cursor WHILE loop to visit (enumerate)  all DB-s.

-- Cursor WHILE loop for all db-s on a SQL Server instance
DECLARE @RunningDB sysname
DECLARE DBcursor CURSOR FAST_FORWARD FOR 
SELECT name FROM master.sys.databases 
WHERE name not in ('master','tempdb','msdb','model')
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @RunningDB
WHILE ( @@FETCH_STATUS = 0)
BEGIN

/********** PROCESSING LOGIC COMES HERE ***********************/

	PRINT @RunningDB
	FETCH NEXT FROM DBcursor INTO @RunningDB
END -- DBcursor WHILE loop
CLOSE DBcursor
DEALLOCATE DBcursor

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 5

Thank you Ewan, Ken, SQLUSA and others for the input. I'm glade i've learn some really cool and new ways to fetch vital meta data. Again thanks to all of you.

Thanks once again