InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 21:23:05 GMT20http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxThanks LynnJimWed, 26 Sep 2012 10:52:58 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspx[quote][b]JC-3113 (9/19/2012)[/b][hr]I am on 2005. Will this not work then ?Jim[/quote]Yes, it will work on SQL Server 2005. You need to check the settings for QUOTED_IDENTIFIER on your server.Just verified after firing up SQL Server 2005 on my laptop.Wed, 19 Sep 2012 09:08:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxI am on 2005. Will this not work then ?JimWed, 19 Sep 2012 08:47:34 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxok got a little fartherno idea what this means:declare @idxname sysname;set @idxname = 'idx_DatePosted';declare @idxname sysname;set @idxname = 'idx_DatePosted';declare @SQLCmd varchar(max);select @SQLCmd = stuff((select 'union all' + char(13) + char(10) + 'select ''' + db.name + ''' as DatabaseName, object_name(idx.object_id) as TableName, idx.name as IndexName from ' + db.name +'.sys.indexes idx where idx.object_id in (select object_id from ' + db.name + '.sys.tables) and idx.name = ''' + @idxname +''' ' + char(13) + char(10)from sys.databases dbwhere db.name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')for xml path(''),type).value('.','varchar(max)'),1,11,'');print @SQLCmd;exec(@SQLCmd);goMsg 1934, Level 16, State 1, Server NSAB-SS80-SQL-N, Line 6SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.Wed, 19 Sep 2012 08:45:44 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxLike this:[code="sql"]declare @idxname sysname;set @idxname = 'idx_DatePosted';[/code]I am finding that I am writing more code using SQL Server 2008 syntax and forget to change it for SQL Server 2005.Wed, 19 Sep 2012 08:31:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspx[quote][b]JC-3113 (9/19/2012)[/b][hr]Hi Lynnreceiving this error executing the code:HResult 0x8B, Level 15, State 1Cannot assign a default value to a local variable.Msg 137, Level 15, State 2, Server NSAB-SS80-SQL-N, Line 5Must declare the scalar variable "@idxname".Jim[/quote]Look at ALL the code I posted. Also, separate the declaration of the variable and the assigning of a value. What I have posted works in SQL Server 2008 and up.Wed, 19 Sep 2012 08:29:35 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxHi Lynnreceiving this error executing the code:HResult 0x8B, Level 15, State 1Cannot assign a default value to a local variable.Msg 137, Level 15, State 2, Server NSAB-SS80-SQL-N, Line 5Must declare the scalar variable "@idxname".JimWed, 19 Sep 2012 08:24:27 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxSomething like this should work:[code="sql"]declare @idxname sysname = 'idx_DatePosted';declare @SQLCmd varchar(max);select @SQLCmd = stuff((select 'union all' + char(13) + char(10) + 'select ''' + db.name + ''' as DatabaseName, object_name(idx.object_id) as TableName, idx.name as IndexName from ' + db.name +'.sys.indexes idx where idx.object_id in (select object_id from ' + db.name + '.sys.tables) and idx.name = ''' + @idxname +''' ' + char(13) + char(10)from sys.databases dbwhere db.name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')for xml path(''),type).value('.','varchar(max)'),1,11,'');print @SQLCmd;exec(@SQLCmd);[/code]Tue, 18 Sep 2012 11:47:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxHi roryp 96873 never heard of quotenamewill look into it.ThanksjimTue, 18 Sep 2012 11:30:50 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspx[quote][b]JC-3113 (9/18/2012)[/b][hr]Hi roryp 96873 I had to modify the code as it does not like '-' in the database name so i bracketed them. I also concatenated the database name to the index so you know where the index resides as it is in more than one database in my case.Jimcode generates sql to find an index in every databaseselect 'select ' + '''[' + name + '].''' + '+ name from [' + name + '].sys.indexes i where i.name = ''AllDocs_PK''' from master.sys.databaseswhere name not in ('master', 'tempdb', 'model', 'msdb');go[/quote]Glad you got it working Jim. Usually the [url=http://msdn.microsoft.com/en-us/library/ms176114.aspx]quotename[/url] function is better than manually adding the brackets though. Your code will most likely be fine, but if there are any names with special characters in them that need escaping, like a square bracket, quotename will account for that. It's unlikely (and probably bad form if you do) you have databases with square brackets in them, but quotename() will handle them.Tue, 18 Sep 2012 11:17:37 GMTroryp 96873http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxHi roryp 96873 I had to modify the code as it does not like '-' in the database name so i bracketed them. I also concatenated the database name to the index so you know where the index resides as it is in more than one database in my case.Jimcode generates sql to find an index in every databaseselect 'select ' + '''[' + name + '].''' + '+ name from [' + name + '].sys.indexes i where i.name = ''AllDocs_PK''' from master.sys.databaseswhere name not in ('master', 'tempdb', 'model', 'msdb');goTue, 18 Sep 2012 11:07:03 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxHi Kingston'even though thi seeems to run, it is geberating an errorThanksJim1> IF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL2> DROP TABLE #tmp_Indexes3>4> DECLARE @strSQL VARCHAR(2000)5> DECLARE @IndexName VARCHAR(1000)6>7> CREATE TABLE #tmp_Indexes8> (9> DatabaseName VARCHAR(100),10> IndexName VARCHAR(1000)11> )12>13> SET @IndexName = 'AllDocs_PK' -- You can enter the name of the index here14>15> SET @strSQL = ' SELECT ''?'', name FROM ?..sysindexes WHERE name LIKE ''%' + @IndexName + '%'' '16>17> INSERT #tmp_Indexes( DatabaseName, IndexName )18> EXECUTE sp_MSforeachdb @strSQL19>20> SELECT * FROM #tmp_Indexes21>22> IF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL23> DROP TABLE #tmp_Indexes24> goMsg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1Incorrect syntax near '-'.Tue, 18 Sep 2012 11:03:10 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxThanks KingstonI will take a look seeJimTue, 18 Sep 2012 09:28:37 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxYou can use the un-documented SP "sp_MSforeachdb" for this[code="sql"]IF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL DROP TABLE #tmp_IndexesDECLARE @strSQL VARCHAR(2000)DECLARE @IndexName VARCHAR(1000)CREATE TABLE #tmp_Indexes( DatabaseName VARCHAR(100), IndexName VARCHAR(1000))SET @IndexName = 'mst_Employees_IX01' -- You can enter the name of the index hereSET @strSQL = ' SELECT ''?'', name FROM ?..sysindexes WHERE name LIKE ''%' + @IndexName + '%'' ' INSERT #tmp_Indexes( DatabaseName, IndexName )EXECUTE sp_MSforeachdb @strSQLSELECT * FROM #tmp_IndexesIF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL DROP TABLE #tmp_Indexes[/code]Edit: Added a comment in the codeTue, 18 Sep 2012 05:36:13 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxThanks very much roryp 96873i will give it a shotJimMon, 17 Sep 2012 12:03:16 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxYou can generate the SQL to look for the index you have in each of your databases by using something like this:[code="sql"]select 'select *from ' + name + '.sys.indexes iwhere i.name = ''typeYourIndexNameHere'''from master.sys.databaseswhere name not in ('master', 'tempdb', 'model', 'msdb')[/code]Might save you a little bit of time.Mon, 17 Sep 2012 11:31:59 GMTroryp 96873http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspx[quote][b]JC-3113 (9/17/2012)[/b][hr]Hi Lynnyou mean i have to look in each database seperately ?no view that encompases al the databases ?Jim[/quote]Basically, yes. There are ways around it but I don't have time to enumerate them at the moment.Mon, 17 Sep 2012 11:21:42 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxHi Lynnyou mean i have to look in each database seperately ?no view that encompases al the databases ?JimMon, 17 Sep 2012 11:11:43 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxThe system view sys.indexes in each database.Mon, 17 Sep 2012 11:07:47 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1360329-1291-1.aspxHi Folkscan comeone tell me the quickest way to find an index in some 100 plus databases ?is there a view or system table that houses that info that I can search through ?All I have is the index nameThanksJimMon, 17 Sep 2012 10:42:55 GMTJC-3113