Code Review

I need to get information on all the unique constraints for a table. I
needed info on whether a constraint is clustered or not, what filegroup
it resides in, columns that comprise the unique index (in a comma
separated list) and it's name. So I wrote the code (see below).
However, SQL is not my first language, so I was looking to see whether a
seasoned SQL expert can recommend anyway to speed up this query. Or
maybe I should take change my approach?
SET NOCOUNT ON
create table #UniqueConstraints
(
ID int IDENTITY,
Name varchar(255),
IndexID int,
IsClustered bit NULL,
FileGroup varchar(255),
Columns varchar(255) NULL
)
create table #ColumnList
(
ID int IDENTITY,
Name varchar(255)
)
-- set to non-clustered by default
insert #UniqueConstraints(Name, IndexID, FileGroup, IsClustered)
SELECT I.name,I.indid,FILEGROUP_NAME(I.groupid) as filegroup, 0
FROM sysindexes I, sysconstraints c
WHERE I.id=OBJECT_ID('dbo.Customers') AND (((I.status & 0x800)=0x800) OR
((I.status & 0x1000)=0x1000))
and i.id = c.id
and (C.status & 0xf)=2
and OBJECT_NAME(C.constid) = I.name
ORDER BY I.indid
-- mark clustered indexees
update #UniqueConstraints
set IsClustered = 1
where IndexID = 1
-- now get columns
DECLARE @.min_ID int, @.indexID int, @.minColumn_ID int
DECLARE @.columnList varchar(255), @.columnName varchar(255), @.comma
varchar(1)
SELECT @.min_ID = min(ID) from #UniqueConstraints
WHILE @.min_ID IS NOT NULL BEGIN
-- get the index id
SELECT @.indexID = IndexID from #UniqueConstraints where ID = @.min_ID
-- freshen up this table
truncate table #ColumnList
-- get the list of involved columns
INSERT #ColumnList(Name)
SELECT COL_NAME(id, colid)
FROM sysindexkeys
WHERE id=OBJECT_ID('dbo.Customers') AND indid=@.indexID ORDER BY
keyno
-- convert the table contents into a comma separated list
SELECT @.minColumn_ID = min(ID) from #ColumnList
-- reinitialize
SET @.comma = ''
SET @.columnList = ''
WHILE @.minColumn_ID IS NOT NULL BEGIN
SELECT @.columnName = Name FROM #ColumnList WHERE ID = @.minColumn_ID
SET @.columnList = @.columnList + @.comma + @.columnName
set @.comma = ','
SELECT @.minColumn_ID = min(ID) FROM #ColumnList WHERE ID >
@.minColumn_ID
END
UPDATE #UniqueConstraints
SET Columns = @.columnList
WHERE ID = @.min_ID
-- get the next value from the table
SELECT @.min_ID = min(ID) FROM #UniqueConstraints WHERE ID > @.min_ID
END
select Name, IsClustered, FileGroup, Columns from #UniqueConstraints
drop table #UniqueConstraints
drop table #ColumnListThe first part of your requirement can be solved in an easier way, like
this:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
The comma separated list of the columns that are part of each unique
constraint is a little bit more complicated. I would use an UDF, like
this:
CREATE FUNCTION dbo.IndexColumns(@.id int, @.indid int)
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @.List nvarchar(4000), @.ColName sysname
DECLARE Columns CURSOR LOCAL READ_ONLY FOR
SELECT COL_NAME(id,colid) FROM sysindexkeys
WHERE id=@.id AND indid=@.indid
ORDER BY keyno
OPEN Columns
WHILE 1=1 BEGIN
FETCH NEXT FROM Columns INTO @.ColName
IF @.@.FETCH_STATUS<>0 BREAK
SET @.List=ISNULL(@.List+',','')+@.ColName
END
CLOSE Columns
DEALLOCATE Columns
RETURN @.List
END
GO
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup,
dbo.IndexColumns(i.id,i.indid) AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
Another way would be to rely on the fact that there are a maximum of 16
columns for an index:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup, (
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=1
)+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=2
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=3
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=4
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=5
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=6
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=7
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=8
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=9
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=10
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=11
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=12
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=13
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=14
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=15
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=16
),'') AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
There is a small difference between the above solutions and your
solution: in the above solutions, the columns in the comma separated
list are presented in the order that is used when the constraint was
defined; in your solution, they are presented in the order in which
they appear in the table.
Razvan