September 17, 2012 at 10:42 am
Hi Folks
can 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 name
Thanks
Jim
September 17, 2012 at 11:07 am
The system view sys.indexes in each database.
September 17, 2012 at 11:11 am
Hi Lynn
you mean i have to look in each database seperately ?
no view that encompases al the databases ?
Jim
September 17, 2012 at 11:21 am
JC-3113 (9/17/2012)
Hi Lynnyou mean i have to look in each database seperately ?
no view that encompases al the databases ?
Jim
Basically, yes. There are ways around it but I don't have time to enumerate them at the moment.
September 17, 2012 at 11:32 am
You can generate the SQL to look for the index you have in each of your databases by using something like this:
select 'select *
from ' + name + '.sys.indexes i
where i.name = ''typeYourIndexNameHere'''
from master.sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb')
Might save you a little bit of time.
September 17, 2012 at 12:03 pm
Thanks very much roryp 96873
i will give it a shot
Jim
September 18, 2012 at 5:36 am
You can use the un-documented SP "sp_MSforeachdb" for this
IF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL
DROP TABLE #tmp_Indexes
DECLARE@strSQLVARCHAR(2000)
DECLARE@IndexName VARCHAR(1000)
CREATE TABLE #tmp_Indexes
(
DatabaseNameVARCHAR(100),
IndexNameVARCHAR(1000)
)
SET@IndexName = 'mst_Employees_IX01' -- You can enter the name of the index here
SET@strSQL = ' SELECT ''?'', name FROM ?..sysindexes WHERE name LIKE ''%' + @IndexName + '%'' '
INSERT#tmp_Indexes( DatabaseName, IndexName )
EXECUTE sp_MSforeachdb @strSQL
SELECT * FROM #tmp_Indexes
IF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL
DROP TABLE #tmp_Indexes
Edit: Added a comment in the code
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 18, 2012 at 9:28 am
Thanks Kingston
I will take a look see
Jim
September 18, 2012 at 11:03 am
Hi Kingston
'
even though thi seeems to run, it is geberating an error
Thanks
Jim
1> IF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL
2> DROP TABLE #tmp_Indexes
3>
4> DECLARE @strSQL VARCHAR(2000)
5> DECLARE @IndexName VARCHAR(1000)
6>
7> CREATE TABLE #tmp_Indexes
8> (
9> DatabaseName VARCHAR(100),
10> IndexName VARCHAR(1000)
11> )
12>
13> SET @IndexName = 'AllDocs_PK' -- You can enter the name of the index here
14>
15> SET @strSQL = ' SELECT ''?'', name FROM ?..sysindexes WHERE name LIKE ''%' + @IndexName + '%'' '
16>
17> INSERT #tmp_Indexes( DatabaseName, IndexName )
18> EXECUTE sp_MSforeachdb @strSQL
19>
20> SELECT * FROM #tmp_Indexes
21>
22> IF OBJECT_ID('tempdb..#tmp_Indexes') IS NOT NULL
23> DROP TABLE #tmp_Indexes
24> go
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 1
Incorrect syntax near '-'.
September 18, 2012 at 11:07 am
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
September 18, 2012 at 11:17 am
JC-3113 (9/18/2012)
Hi roryp 96873I 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.
September 18, 2012 at 11:30 am
Hi roryp 96873
never heard of quotename
will look into it.
Thanks
jim
September 18, 2012 at 11:47 am
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);
September 19, 2012 at 8:24 am
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
September 19, 2012 at 8:29 am
JC-3113 (9/19/2012)
Hi Lynnreceiving 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.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply