Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: variable question

    We have a number of MS SQLs, and more are brought in by consultants.
    I thought I would write a little script to quickly, give me a list of databases and their tables and eventually table column info. But I am stuck in the first step.

    DECLARE crs_dbname INSENSITIVE CURSOR
    FOR SELECT name FROM [master]..sysdatabases WHERE name <> 'Northwind ' AND name <> 'pubs'
    OPEN crs_dbname
    DECLARE @dbname varchar(50)
    WHILE @@FETCH_STATUS <> -1
    BEGIN
    FETCH NEXT FROM crs_dbname INTO @dbname
    PRINT @dbname
    EXEC("SELECT TABLE_NAME FROM " + [@dbname].INFORMATION_SCHEMA.tables)
    END
    CLOSE crs_dbname
    DEALLOCATE crs_dbname

    The failure is in the EXEC SELECT
    If I run the select alone and add in the dbname manually it works fine.
    SELECT TABLE_NAME FROM [databaseName].INFORMATION_SCHEMA.tables

    Any ideas?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: variable question

    Originally posted by rcarlson
    We have a number of MS SQLs, and more are brought in by consultants.
    I thought I would write a little script to quickly, give me a list of databases and their tables and eventually table column info. But I am stuck in the first step.

    DECLARE crs_dbname INSENSITIVE CURSOR
    FOR SELECT name FROM [master]..sysdatabases WHERE name <> 'Northwind ' AND name <> 'pubs'
    OPEN crs_dbname
    DECLARE @dbname varchar(50)
    WHILE @@FETCH_STATUS <> -1
    BEGIN
    FETCH NEXT FROM crs_dbname INTO @dbname
    PRINT @dbname
    EXEC("SELECT TABLE_NAME FROM " + [@dbname].INFORMATION_SCHEMA.tables)
    END
    CLOSE crs_dbname
    DEALLOCATE crs_dbname

    The failure is in the EXEC SELECT
    If I run the select alone and add in the dbname manually it works fine.
    SELECT TABLE_NAME FROM [databaseName].INFORMATION_SCHEMA.tables

    Any ideas?
    You have to use this :

    EXEC("SELECT TABLE_NAME FROM " + @dbname+".INFORMATION_SCHEMA.tables")

  3. #3
    Join Date
    Sep 2003
    Posts
    4

    Re: variable question

    Originally posted by snail
    You have to use this :

    EXEC("SELECT TABLE_NAME FROM " + @dbname+".INFORMATION_SCHEMA.tables")
    Thanks but it returns a Syntax error for the select and does not run -
    I altered the statement to:
    EXEC("SELECT TABLE_NAME FROM " + [@dbname]+".[INFORMATION_SCHEMA].tables") - still nothing?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have a database that stores server names, databases, objects (tables, procedures, triggers, etc), relationships and application information. The data is populated by a nightly procedure called DOCUMENT_SERVER which in turn calls another procedure DOCUMENT_DATABASE for each DB on the server.

    I can send it to you if you are interested in using or looking at the code.

    blindman

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: variable question

    Originally posted by rcarlson
    Thanks but it returns a Syntax error for the select and does not run -
    I altered the statement to:
    EXEC("SELECT TABLE_NAME FROM " + [@dbname]+".[INFORMATION_SCHEMA].tables") - still nothing?
    Remove brackets [] - it will work.

    declare @dbname varchar(10)
    set @dbname='testdb'
    EXEC('SELECT TABLE_NAME FROM ' + @dbname + '.INFORMATION_SCHEMA.TABLES')

  6. #6
    Join Date
    Sep 2003
    Posts
    4
    Originally posted by blindman
    I have a database that stores server names, databases, objects (tables, procedures, triggers, etc), relationships and application information. The data is populated by a nightly procedure called DOCUMENT_SERVER which in turn calls another procedure DOCUMENT_DATABASE for each DB on the server.

    I can send it to you if you are interested in using or looking at the code.

    blindman
    Thanks I would be interested...sounds like more than I need, but please send.

Posting Permissions

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