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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
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
-
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
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
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
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
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