InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 10:50:38 GMT20http://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxsp_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.Tue, 06 Nov 2012 00:56:18 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspx"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.Tue, 06 Nov 2012 00:43:14 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxEXEC sp_MSforeachdb 'use [?]; select * from information_schema.columns ':cool:Tue, 06 Nov 2012 00:03:29 GMTjeetsingh.cshttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxHi,I have taken CHAR(13) off from query now and still works, so the sql query looks like the below now:[code="other"]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 @SQLEXECUTE sp_executesql @SQL[/code]Mon, 05 Nov 2012 09:44:35 GMTSQL_Killshttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspx[quote][b]anthony.green (11/5/2012)[/b][hr]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.[/quote]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.DrewMon, 05 Nov 2012 09:13:41 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxHi, I have amended your code so that all appears in one select statement.[code="other"]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 @SQLEXECUTE sp_executesql @SQL[/code]Mon, 05 Nov 2012 07:53:57 GMTSQL_Killshttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxNice one Thanks!Mon, 05 Nov 2012 05:26:19 GMTSQL_Killshttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxThe 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.Mon, 05 Nov 2012 05:22:05 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxHi,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
Mon, 05 Nov 2012 05:18:28 GMTSQL_Killshttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxSomething like the following will do the trick[code="sql"]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[/code]Mon, 05 Nov 2012 05:06:35 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1380991-392-1.aspxHi,When I run the following query:[code="sql"]Select *From INFORMATION_SCHEMA.COLUMNS[/code]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?ThanksMon, 05 Nov 2012 04:58:26 GMTSQL_Kills