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

Quickest Way to Find an Index Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 11:17 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 740, Visits: 6,218
JC-3113 (9/18/2012)
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.

Jim

code generates sql to find an index in every database

select
'select ' + '''[' + name + '].''' + '+ name from [' + name + '].sys.indexes i where i.name = ''AllDocs_PK'''
from
master.sys.databases
where
name not in
(
'master',
'tempdb',
'model',
'msdb'
);
go


Glad you got it working Jim. Usually the quotename 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.
Post #1360912
Posted Tuesday, September 18, 2012 11:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Hi roryp 96873




never heard of quotename

will look into it.

Thanks
jim
Post #1360917
Posted Tuesday, September 18, 2012 11:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:46 PM
Points: 20,792, Visits: 32,706
Something like this should work:


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 db
where db.name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
for xml path(''),type).value('.','varchar(max)'),1,11,'')
;

print @SQLCmd;
exec(@SQLCmd);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1360924
Posted Wednesday, September 19, 2012 8:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Hi Lynn

receiving this error executing the code:

HResult 0x8B, Level 15, State 1
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Server NSAB-SS80-SQL-N, Line 5
Must declare the scalar variable "@idxname".

Jim
Post #1361393
Posted Wednesday, September 19, 2012 8:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:46 PM
Points: 20,792, Visits: 32,706
JC-3113 (9/19/2012)
Hi Lynn

receiving this error executing the code:

HResult 0x8B, Level 15, State 1
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Server NSAB-SS80-SQL-N, Line 5
Must declare the scalar variable "@idxname".

Jim


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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361397
Posted Wednesday, September 19, 2012 8:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:46 PM
Points: 20,792, Visits: 32,706
Like this:


declare @idxname sysname;
set @idxname = 'idx_DatePosted';


I am finding that I am writing more code using SQL Server 2008 syntax and forget to change it for SQL Server 2005.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361398
Posted Wednesday, September 19, 2012 8:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
ok got a little farther
no 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 db
where db.name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
for xml path(''),type).value('.','varchar(max)'),1,11,'');


print @SQLCmd;
exec(@SQLCmd);

go


Msg 1934, Level 16, State 1, Server NSAB-SS80-SQL-N, Line 6
SELECT 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.
Post #1361405
Posted Wednesday, September 19, 2012 8:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
I am on 2005.

Will this not work then ?

Jim
Post #1361407
Posted Wednesday, September 19, 2012 9:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:46 PM
Points: 20,792, Visits: 32,706
JC-3113 (9/19/2012)
I am on 2005.

Will this not work then ?

Jim


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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1361423
Posted Wednesday, September 26, 2012 10:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Thanks Lynn

Jim
Post #1364840
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse