none
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 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.

    Thursday, November 12, 2009 4:41 PM

Answers

  • 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
    • Marked as answer by CRM_Newbie Thursday, November 12, 2009 9:48 PM
    • Unmarked as answer by CRM_Newbie Thursday, November 12, 2009 9:49 PM
    • Marked as answer by CRM_Newbie Thursday, November 12, 2009 9:52 PM
    Thursday, November 12, 2009 6:30 PM

All replies

  • 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.
    Thursday, November 12, 2009 5:49 PM
  • 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)
    Thursday, November 12, 2009 5:57 PM
  • 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
    • Marked as answer by CRM_Newbie Thursday, November 12, 2009 9:48 PM
    • Unmarked as answer by CRM_Newbie Thursday, November 12, 2009 9:49 PM
    • Marked as answer by CRM_Newbie Thursday, November 12, 2009 9:52 PM
    Thursday, November 12, 2009 6:30 PM
  • 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
    Thursday, November 12, 2009 6:32 PM
    Moderator
  • 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
    Thursday, November 12, 2009 9:51 PM