Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Quickest Way to Find an Index


Quickest Way to Find an Index

Author
Message
roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 6597
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.
JC-3113
JC-3113
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 620
Hi roryp 96873




never heard of quotename

will look into it.

Thanks
jim
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24150 Visits: 37920
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);




Cool
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)
JC-3113
JC-3113
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24150 Visits: 37920
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.

Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24150 Visits: 37920
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.

Cool
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)
JC-3113
JC-3113
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 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.
JC-3113
JC-3113
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 620
I am on 2005.

Will this not work then ?

Jim
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24150 Visits: 37920
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.

Cool
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)
JC-3113
JC-3113
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 620
Thanks Lynn

Jim
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search