October 11, 2011 at 10:59 pm
Hi I need to list indexes in a SQL Server...for that I have written a cursor which will find all indexes.. then i used a nested cursor where I can use database name & then cursor for index tables in that db.... but somewhere I am lacking... please help with the code...
SELECT tblname = CASE WHEN ik.keyno = 1 THEN o.name ELSE '' END,
ixname = CASE WHEN ik.keyno = 1 THEN i.name ELSE '' END,
ik.keyno, colname = c.name,
isdesc = CASE indexkey_property(o.id, i.indid, ik.keyno,
'IsDescending')
WHEN 1 THEN 'DESC'
ELSE ''
END
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid = 1
ORDER BY o.name, ik.keyno
Sagar Sonawane
** Every DBA has his day!!:cool:
October 12, 2011 at 12:21 am
it is not clear what is your intention, why u r going for a cursor u can easily find all indexes and and db name.
will you please explain in a broader way that we can able to understand your requirement.
October 12, 2011 at 12:28 am
first of all thank you for your reply....
I am creating an Inventory Application for all Linked servers... for that I have scripted rest all things like DB info, logins, Drive Size, etc,.
I am stucked at Index info... I tried using (select name ,index_id ,OBJECT_ID ,TYPE ,type_desc ,is_unique ,is_primary_key ,fill_factor from ['+@srvname+'].master.sys.indexes')
IT shows only System indexes... It doesnt list User defined indexes....
I need a code for Listing all indexes for a SQL server instance...
Sagar Sonawane
** Every DBA has his day!!:cool:
October 12, 2011 at 12:52 am
>>I am stucked at Index info... I tried using (select name ,index_id ,OBJECT_ID ,TYPE ,type_desc ,is_unique ,is_primary_key ,fill_factor from ['+@srvname+'].master.sys.indexes'<<
i think here instead of using master u have to use the instance name
select * FROM AdventureWorks.sys.indexes
this query will result you all the syetem as well as user defined indexes.
October 12, 2011 at 12:57 am
My perspective for using cursor is.... In one query it should give all indexes information for all linked servers.... hence i m not using Instance name....
Sagar Sonawane
** Every DBA has his day!!:cool:
October 12, 2011 at 1:06 am
i think you shouldn't inculed master in your code to get the user defined indexes,
can u pls post your complete code, that we can go through and come up with a solution.
October 12, 2011 at 1:10 am
I was using this code to list all Indexes from linked servers..... but IT shows only system defined indexes...
I am using SQL Server 2008 R2
IF EXISTS(SELECT * FROM
SYSOBJECTS WHERE name = 'indexinfo' AND type = 'U')
BEGIN
DROP TABLE indexinfo
END
create table indexinfo(servername nvarchar(50),name varchar(200),index_id int,OBJECT_ID int,TYPE tinyint,type_desc varchar(500) ,is_unique tinyint,is_primary_key tinyint,fill_factor tinyint)
create table #temp2(name varchar(200),index_id int,OBJECT_ID int,TYPE tinyint,type_desc varchar(500) ,is_unique tinyint,is_primary_key tinyint,fill_factor tinyint)
create table #servtemp1(servername nvarchar(50))
DECLARE @srvname varchar(50) -- Servername
DECLARE @getsrvname CURSOR -- Declare Cursor
SET @getsrvname = CURSOR FOR SELECT name FROM master.sys.servers where is_linked <> 0 -- SET cursor statement
OPEN @getsrvname
FETCH NEXT
FROM @getsrvname INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #servtemp1 values(@srvname)
insert into #temp2 exec ('select name ,index_id ,OBJECT_ID ,TYPE ,type_desc ,is_unique ,is_primary_key ,fill_factor from ['+@srvname+'].master.sys.indexes')
exec('select * from #servtemp1,#temp2')
insert into indexinfo exec('select * from #servtemp1,#temp2')
truncate table #temp2
truncate table #servtemp1
FETCH NEXT
FROM @getsrvname INTO @srvname
END
--print('select * from indexinfo')
drop table #servtemp1
drop table #temp2
CLOSE @getsrvname
DEALLOCATE @getsrvname
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply