Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Information_Schema.Columns on All Database


Information_Schema.Columns on All Database

Author
Message
SQL_Kills
SQL_Kills
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 799
Hi,

When I run the following query:

Select *
From INFORMATION_SCHEMA.COLUMNS




This only returns results for the Databse it is on. Is there script that anyone knows that will loop through all databses on that server?

Thanks
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
Something like the following will do the trick

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT * FROM INFORMATION_SCHEMA.COLUMNS' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' & # x 0 D ; ',CHAR(13) + CHAR(10)
)
SELECT @SQL
--EXECUTE sp_executesql @SQL





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


SQL_Kills
SQL_Kills
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 799
Hi,

Thanks for quick reply, when I ran this I am getting unknown characters which does not work when I execute the SQL for this:

USE [master];
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
The code is sound when looked at in the editior but due to it being a XML character it strips it out in the main view.

If you remove the spaces in the ' & # x 0 D ; ' part of the script it will work. The script has been amended to ensure it shows that part if it now.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


SQL_Kills
SQL_Kills
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 799
Nice one Thanks!
SQL_Kills
SQL_Kills
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 799
Hi, I have amended your code so that all appears in one select statement.


DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = substring(REPLACE(
CAST(
(
SELECT ' UNION SELECT * FROM ' + QUOTENAME(name)
--Select *
--'SELECT * FROM '+ QUOTENAME(name)
+'.'+'INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ''FindMe'''
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
),8,8000)
--SELECT @SQL
EXECUTE sp_executesql @SQL




drew.allen
drew.allen
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2617 Visits: 9894
anthony.green (11/5/2012)
The code is sound when looked at in the editior but due to it being a XML character it strips it out in the main view.

If you remove the spaces in the ' & # x 0 D ; ' part of the script it will work. The script has been amended to ensure it shows that part if it now.


There's an even easier approach. Just leave CHAR(13) out of your SQL scripts that are constructed using FOR XML. Both CHAR(13) and CHAR(10) are treated as whitespace and whitespace is all equivalent in SQL scripts. FOR XML entitizes CHAR(13), but does not entitize CHAR(10) and the XML editor in SSMS will treat CHAR(10) the same as CHAR(13) + CHAR(10). You gain nothing by including CHAR(13) and create problems by doing so. Just leave it out.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
SQL_Kills
SQL_Kills
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 799
Hi,

I have taken CHAR(13) off from query now and still works, so the sql query looks like the below now:


DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = substring(REPLACE(
CAST(
(
SELECT ' UNION SELECT * FROM ' + QUOTENAME(name)
--Select *
--'SELECT * FROM '+ QUOTENAME(name)
+'.'+'INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ''findme'''
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',+ CHAR(10)
),8,8000)
--SELECT @SQL
EXECUTE sp_executesql @SQL



jeetsingh.cs
jeetsingh.cs
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 618
EXEC sp_MSforeachdb 'use [?];
select * from information_schema.columns
'


Cool
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
"sp_MSforeach.." procedures are undocumented and according to Microsoft are for Internal use only.
If what you are trying to do is an Adhoc requirement then it is fine to use this method. But if it is going to be a part of a routine then it is certainly not advised to use it.
There must be some reason that Microsoft has kept these procedures undocumented and the first reason I can think is the reliability of these procedures in certain scenarios.

So, my advice would be to avoid "sp_MSforeach.." procedures and use the script if its a routine requirement.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search