Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: Getting the INFORMATION_SCHEMA.TABLES from a databasename.

    Hello

    I want to achieve something like this:

    Code:
    DECLARE @dbName nvarchar(100)
    
    SET @dbName = 'TestDatabase'
    SELECT * FROM 
    @dbName.INFORMATION_SCHEMA.TABLES
    But of course, the string, ehm, the nvarchar @dbName does not have the property INFORMATION_SCHEMA on it, so, is it possible to cast the dbName into a database-object, so the INFORMATION_SCHEMA lives...?

    Edit: This could have been solved if a stored procedure/function allowed the "use @dbName" usage within it...

    So, this is not possible? I am creating a stored procedure which takes the DatbaseName [CATALOG_NAME, nvarchar type] and based on it, I wanna query the INFORMATION_SCHEMA... But my issue is that the "INFORMATION_SCHEMA" uses the last "use <Catalog_Name>" entered, which makes sense, but not when creating a global stored procedure under "master".

    Edit2:
    I want to be able to do something along these lines (yes I know tables do not containg a catalog_name, and the name from sys.database is actually TABLE_CATALOG... :P ):
    Code:
    SELECT * FROM sys.database as t1
    INNER JOIN INFORMATION_SCHEMA_TABLES as t2 ON t1.catalog_name = t2.catalog_name
    Which then should've printed something along these lines
    Code:
    catalog       tablename
    Users         User
    Users         History
    Users         Address
    Edit3:
    Code:
    DECLARE @temp NVARCHAR(255)
    SET @temp = (SELECT TOP(1) name FROM sys.databases ORDER BY name DESC)
    
    DECLARE @query NVARCHAR(255)
    SET @query = 'USE ' + @temp + '; '
    EXEC(@query)
    
    SELECT @temp
    SELECT * FROM INFORMATION_SCHEMA.TABLES
    did not work... So in short: Selection all tables from all databases, is what I want.
    Last edited by ManyTimes; 07-19-13 at 05:53.

  2. #2
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    Bump.

    Is there no other way to switch the catalog queries are ran against, other than using
    "use <Catalog-name>;"?

    Really want a stored procedure to take a catalogname (nvarchar) and within the stored procedure call change the catalogname (ignoring checking for errors, such as catalog-access for user...).

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       INTO #foo
       FROM INFORMATION_SCHEMA.TABLES
       WHERE 0 = 1
    
    EXECUTE sp_MSforeachdb 'INSERT INTO #foo
       SELECT *
          FROM [?].INFORMATION_SCHEMA.TABLES'
    
    SELECT *
       FROM #foo
       ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Minor modifications to Pat's script
    Code:
    SELECT DB_Name() As database_name, *
       INTO #foo
       FROM INFORMATION_SCHEMA.TABLES
       WHERE 0 = 1
    
    EXECUTE sp_MSforeachdb 'INSERT INTO #foo
       SELECT ''?'', *
          FROM [?].INFORMATION_SCHEMA.TABLES'
    
    SELECT *
       FROM #foo
       ORDER BY database_name, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    gvee, why do you duplicate the TABLE_CATALOG column as database_name?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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