How to run a query to bring all the fields from each table

  • Hello,

    I need a query to bring all the fields for all tables from each database

    any ideas?

    thanks

  • You can use sp_msforeachdb and set @command1 to "Select table_schema, table_name, column_name, data_type from infomation_schema.columns".

    [font="Courier New"]sp_msforeachdb @command1 = 'Select table_schema, table_name, column_name, data_type from INFORMATION_SCHEMA.COLUMNS'[/font]

    Here's an article about sp_msforeachdb: http://www.databasejournal.com/features/mssql/article.php/3441031

  • that's it..THANK YOU !!!!!!!!!!!!!!

  • Use information_schema.columns

    U you query sys.databases, and append the database name to the information_schema.columns.

    SELECT

    'SELECT *

    FROM '+ NAME + '.INFORMATION_SCHEMA.COLUMNS

    ORDER BY table_name, ordinal_position'

    FROM sys.sysdatabases

    WHERE name not in ('tempdb', 'master', 'model', 'msdb')

  • query sys.columns or INFORMATION_SCHEMA.COLUMNS. You will probably need to link in a few other tables or views as well to know what you are looking at.

    😎

  • Heh... I just can't bring myself to use something that has a cursor in it like sp_msforeachdb does in the presence of VARCHAR(MAX)... 😛

    --===== Code to return all table names and column names across all databases

    -- Jeff Moden

    DECLARE @sql VARCHAR(MAX)

    --===== Create a UNION'ed command to return the info from all databases

    -- as a single result set.

    SELECT @sql = COALESCE(@SQL+' UNION ALL'+CHAR(10),'')

    + REPLACE(

    ' SELECT ''**DBName**'' AS DB_Name,

    Table_Schema COLLATE Latin1_General_BIN AS Table_Schema,

    Table_Name COLLATE Latin1_General_BIN AS Table_Name,

    Column_Name COLLATE Latin1_General_BIN AS Column_Name,

    Data_Type COLLATE Latin1_General_BIN AS Data_Type

    FROM [**DBName**].INFORMATION_SCHEMA.COLUMNS'+CHAR(10),

    '**DBName**',Name)

    FROM Master.sys.SysDatabases

    WHERE DBID > 4 --Don't include MASTER, TEMPDB, MODEL, or MSDB

    --===== Display the assembled command (comment out for production use)

    PRINT (@SQL)

    --===== Execute the assembled command to return a single result set.

    -- (Unlike sp_MSForEachDB which returns 1 result set per DB)

    EXEC (@SQL)

    The side benefit of the code above is that it returns a single result set instead of one result set for each db. That means you can easily insert the results into a temp table...

    ... and it blows the doors off of sp_MSForEachDB.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jack Corbett (8/4/2008)


    You can use sp_msforeachdb and set @command1 to "Select table_schema, table_name, column_name, data_type from infomation_schema.columns".

    [font="Courier New"]sp_msforeachdb @command1 = 'Select table_schema, table_name, column_name, data_type from INFORMATION_SCHEMA.COLUMNS'[/font]

    Here's an article about sp_msforeachdb: http://www.databasejournal.com/features/mssql/article.php/3441031

    Hi Jack,

    I'm thinking there needs to be a USE in the dynamic SQL...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff..

    I executed your script and, surprisingly, got an error. One of my databases was in the recovering status and raised error on that database.

    So I altered your script a little bit in order to skip the database in the restoring state...

    DECLARE @sql VARCHAR(MAX)

    --===== Create a UNION'ed command to return the info from all databases

    -- as a single result set.

    SELECT @sql = COALESCE(@SQL+' UNION ALL'+CHAR(10),'')

    + REPLACE(

    ' SELECT ''**DBName**'' AS DB_Name,

    Table_Schema COLLATE Latin1_General_BIN AS Table_Schema,

    Table_Name COLLATE Latin1_General_BIN AS Table_Name,

    Column_Name COLLATE Latin1_General_BIN AS Column_Name,

    Data_Type COLLATE Latin1_General_BIN AS Data_Type

    FROM [**DBName**].INFORMATION_SCHEMA.COLUMNS'+CHAR(10),

    '**DBName**',Name)

    FROM Master.sys.sysdatabases

    WHERE dbid > 4 --Don't include MASTER, TEMPDB, MODEL, or MSDB

    AND DATABASEPROPERTYEX(DB_NAME(dbid), 'Status') = 'ONLINE'

    --===== Display the assembled command (comment out for production use)

    PRINT (@SQL)

    --===== Execute the assembled command to return a single result set.

    -- (Unlike sp_MSForEachDB which returns 1 result set per DB)

    EXEC (@SQL)

    😀

    Atif Sheikh

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Jeff Moden (8/4/2008)


    Jack Corbett (8/4/2008)


    You can use sp_msforeachdb and set @command1 to "Select table_schema, table_name, column_name, data_type from infomation_schema.columns".

    [font="Courier New"]sp_msforeachdb @command1 = 'Select table_schema, table_name, column_name, data_type from INFORMATION_SCHEMA.COLUMNS'[/font]

    Here's an article about sp_msforeachdb: http://www.databasejournal.com/features/mssql/article.php/3441031

    Hi Jack,

    I'm thinking there needs to be a USE in the dynamic SQL...

    Hmmm, "USE" is a pain in Dynamic SQL, because it has to be the first command in a batch, you have to start nesting contexts. Its probably easier to qualify the table name with the database:

    [font="Courier New"]sp_msforeachdb @command1 = 'Select table_schema, table_name, column_name, data_type from [' + Name + '].INFORMATION_SCHEMA.COLUMNS'[/font]

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Jeff Moden (8/4/2008)


    Jack Corbett (8/4/2008)


    You can use sp_msforeachdb and set @command1 to "Select table_schema, table_name, column_name, data_type from infomation_schema.columns".

    [font="Courier New"]sp_msforeachdb @command1 = 'Select table_schema, table_name, column_name, data_type from INFORMATION_SCHEMA.COLUMNS'[/font]

    Here's an article about sp_msforeachdb: http://www.databasejournal.com/features/mssql/article.php/3441031

    Hi Jack,

    I'm thinking there needs to be a USE in the dynamic SQL...

    Right. There doesn't have to be a USE, Mr. Young has the right idea. It should be:

    [font="Courier New"]sp_msforeachdb @command1 = 'Select table_schema, table_name, column_name, data_type from [?].INFORMATION_SCHEMA.COLUMNS'

    [/font]

    The procedure replaces the "?" with the database name.

    No doubt that Jeff's solution is faster (as usual), but mine was easier to type.:P

    Also mine does take advantage of a undocumented (by MS) procedure that could change or disappear so using sys.databases and the dynamic SQL is probably better.

    I am also against using cursor (RBAR) solutions most of the time and if this is something being run often I would not use sp_msforeachdb, but would propose a set-based solution, but for a one-time get it done solution I use it.

  • Jack Corbett (8/5/2008)


    Right. There doesn't have to be a USE, Mr. Young has the right idea.

    Heh, "Mr. Young"?!? Heavens, I'm old but I'll never be that old. Please, call me Barry.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I don't know, based on your time in the field from your recently posted article, I feel like I need to show you proper respect and call you "Mister". I could call you "Pops" if you prefer. :w00t:

  • Jack Corbett (8/5/2008)


    I don't know, based on your time in the field from your recently posted article, I feel like I need to show you proper respect and call you "Mister". I could call you "Pops" if you prefer. :w00t:

    Ouch! OK, as soon as I find my dentures and my walker, you're gonna pay Buddy!

    😛

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Jack Corbett (8/5/2008)


    Also mine does take advantage of a undocumented (by MS) procedure that could change or disappear so using sys.databases and the dynamic SQL is probably better.

    Heck... I wouldn't worry about that, Jack... they even take away things that ARE documented. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • rbarryyoung (8/5/2008)


    Jack Corbett (8/5/2008)


    I don't know, based on your time in the field from your recently posted article, I feel like I need to show you proper respect and call you "Mister". I could call you "Pops" if you prefer. :w00t:

    Ouch! OK, as soon as I find my dentures and my walker, you're gonna pay Buddy!

    😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

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