Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    28

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

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't do this without dynamic SQL and a loop. I wouldn't use "USE". Instead I would do something like:

    Code:
    UPDATE dbo.TotalDatabase
    SET NoOfTable = (SELECT COUNT(*) 
    FROM [databasename].sys.tables)
    WHERE DbName = [databasename]
    That is your base string - use REPLACE to substitute the placeholder ([databasename]) with the actual database name.

    I wouldn't do this in a cursor - dynamic cursor syntax is a ball ache - I would just use a loop.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummmmmmmm...why not use the system cat?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2009
    Posts
    28
    Thanks a lot for everybody's input on my thread. I get my problem solved though this amazing stored procedure which Microsoft provides

    sp_msForEachdb 'INSERT INTO #DbReport
    SELECT
    ''?'',
    NULL,
    getdate(),
    COUNT(name) AS CNT
    from ?.sys.objects
    WHERE type_desc = ''USER_TABLE'' '
    Also sp_msForEachTable is very useful too.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •