November 5, 2012 at 4:58 am
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
November 5, 2012 at 5:06 am
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
November 5, 2012 at 5:18 am
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
November 5, 2012 at 5:22 am
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.
November 5, 2012 at 5:26 am
Nice one Thanks!
November 5, 2012 at 7:53 am
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
November 5, 2012 at 9:13 am
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
November 5, 2012 at 9:44 am
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
November 6, 2012 at 12:03 am
EXEC sp_MSforeachdb 'use [?];
select * from information_schema.columns
'
😎
November 6, 2012 at 12:43 am
"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.
November 6, 2012 at 12:56 am
sp_msforeachdb also doesnt take into account certain characters in the DB Name, so it wont work as intended if such characters exist. The methods advised of going through sys.databases and using Quotename gets around this issue.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply