Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Information_Schema.Columns on All Database Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 4:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:45 PM
Points: 111, Visits: 600
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
Post #1380991
Posted Monday, November 5, 2012 5:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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
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

Post #1380999
Posted Monday, November 5, 2012 5:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:45 PM
Points: 111, Visits: 600
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
Post #1381009
Posted Monday, November 5, 2012 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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
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

Post #1381012
Posted Monday, November 5, 2012 5:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:45 PM
Points: 111, Visits: 600
Nice one Thanks!
Post #1381017
Posted Monday, November 5, 2012 7:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:45 PM
Points: 111, Visits: 600
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



Post #1381090
Posted Monday, November 5, 2012 9:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1381145
Posted Monday, November 5, 2012 9:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:45 PM
Points: 111, Visits: 600
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


Post #1381171
Posted Tuesday, November 6, 2012 12:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:38 AM
Points: 251, Visits: 613
EXEC sp_MSforeachdb 'use [?];
select * from information_schema.columns
'


Post #1381401
Posted Tuesday, November 6, 2012 12:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:13 AM
Points: 1,127, Visits: 1,594
"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
Post #1381421
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse