Post reply

query help

  • April 12, 2004 at 1:03 pm

    #85167

    I need a query that can grab server information.

    Database names, tables names and column names.

    I was hoping that there is some stored procedure that I can run that will perfom this before I attempt to write one.

     

    Thanks

  • April 12, 2004 at 1:30 pm

    #502470

    For databases you can fire

    SELECT * FROM MASTER..SYSDATABASES

    For the rest take a look at the INFORMATION_SCHEMA views in BOL. Especially INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.

    Also worth searchgin is the script section here.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 12, 2004 at 11:03 pm

    #502491

    there is even a view of databases: INFORMATION_SCHEMA.SCHEMAS

    Andy Hilliard
    Owl Creek Consulting[/url]

  • April 13, 2004 at 1:04 am

    #502498

    Yeah, you're right!

    With a correction in the typo; it's INFORMATION_SCHEMA.SCHEMATA

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 13, 2004 at 8:31 am

    #502572

    Look at sp_columns and sp_tables

  • April 15, 2004 at 7:48 am

    #502931

    Thanks guys,

    But what I was really looking for is 1 query that grabs all the following data

    Database,table and columns through the whole server. So that i get a list by just running this query and I can move from server to server running the same query

  • April 15, 2004 at 8:07 am

    #502948

    Well, there is an undocumented s_proc sp_MSforeachdb in master, which accepts a command and executes this in every db on the server.

    As I don't use it, I can't remember the syntax, but I'm sure you'll find something on this s_proc here on the site. Might be worth a try.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 15, 2004 at 8:35 am

    #502954

    This will do it

    Create table #DB_Name (dbname varchar (100))

    Create table #results (dbname varchar (100), tabName varchar (100), colName varchar (100))

    Insert into #DB_Name(dbname)

    select [name] from master.dbo.sysdatabases where dbid > 6

    DECLARE @dbName varchar (100),  @sql varchar (1000)

    set @dbName = ''

    DECLARE db_Update  CURSOR

    FOR

     SELECT dbname  from #DB_name

      OPEN db_Update

     FETCH NEXT FROM db_Update INTO @dbName

      WHILE (@@fetch_status <> -1)

       BEGIN

      IF (@@fetch_status <> -2)

      BEGIN

    set @sql = 'use ['+@dbname+ '] '

    set @sql = @sql +' Insert into #results(dbName, TabName, colName) SELECT '

    set @sql = @sql + ''''+@dbname+''''+ ' as dbName,table_name, column_name

    FROM Information_Schema.Columns'

    Exec (@sql) 

      END

     FETCH NEXT FROM db_Update INTO @dbName

    END

    CLOSE db_Update

    DEALLOCATE db_Update

    Select * from #results

    drop table #db_name

    drop table #results


    Thanks, and don't forget to Chuckle

  • April 15, 2004 at 10:42 am

    #502986

    Thanks alot that script was perfect

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply