I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes. So my
questions is a) good idea? and b) how? There are foreign key references
to most of them so those would need to be dropped first and then
re-created after the clustered one was created and that could cascade
(I think?)
Any existing scripts out there that might do this? I found something
similar and modified it, the sql is included below. This gives me the
list of all the columns I need, I just need to get the foreign keys for
each from here before each one and generate all the create/drop
scripts.
All the columns I am looking to do this for are called "Id" making this
somewhat simpler. I'm just looking to incrementally make the SQL side
better and don't want to rewrite a bunch of application level code to
make the column names ISO compliant, etc.
/*
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexCol umnOrder
(
@object_id INT,
@index_id TINYINT,
@column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @r NVARCHAR(5)
SELECT @r = CASE INDEXKEY_PROPER TY
(
@object_id,
@index_id,
@column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @r
END
-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexCol umns
(
@table_name SYSNAME,
@object_id INT,
@index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@colnames NVARCHAR(4000),
@thisColID INT,
@thisColName SYSNAME
SET @colnames = INDEX_COL(@tabl e_name, @index_id, 1)
+ dbo.GetIndexCol umnOrder(@objec t_id, @index_id, 1)
SET @thisColID = 2
SET @thisColName = INDEX_COL(@tabl e_name, @index_id, @thisColID)
+ dbo.GetIndexCol umnOrder(@objec t_id, @index_id, @thisColID)
WHILE (@thisColName IS NOT NULL)
BEGIN
SET @thisColID = @thisColID + 1
SET @colnames = @colnames + ', ' + @thisColName
SET @thisColName = INDEX_COL(@tabl e_name, @index_id,
@thisColID)
+ dbo.GetIndexCol umnOrder(@objec t_id, @index_id,
@thisColID)
END
RETURN @colNames
END
CREATE VIEW dbo.vAllIndexes
AS
begin
SELECT
TABLE_NAME = OBJECT_NAME(i.i d),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexCol umns(OBJECT_NAM E(i.id), i.id,
i.indid),
IS_CLUSTERED = INDEXPROPERTY(i .id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i .id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS :
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY( i.id, 'IsMsShipped') = 0
end
*/
SELECT
v.*
FROM
dbo.vAllIndexes v
INNER JOIN
INFORMATION_SCH EMA.TABLE_CONST RAINTS T
ON
T.CONSTRAINT_NA ME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TY PE = 'PRIMARY KEY'
AND v.COLUMN_LIST = 'Id'
AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAME
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes. So my
questions is a) good idea? and b) how? There are foreign key references
to most of them so those would need to be dropped first and then
re-created after the clustered one was created and that could cascade
(I think?)
Any existing scripts out there that might do this? I found something
similar and modified it, the sql is included below. This gives me the
list of all the columns I need, I just need to get the foreign keys for
each from here before each one and generate all the create/drop
scripts.
All the columns I am looking to do this for are called "Id" making this
somewhat simpler. I'm just looking to incrementally make the SQL side
better and don't want to rewrite a bunch of application level code to
make the column names ISO compliant, etc.
/*
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexCol umnOrder
(
@object_id INT,
@index_id TINYINT,
@column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @r NVARCHAR(5)
SELECT @r = CASE INDEXKEY_PROPER TY
(
@object_id,
@index_id,
@column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @r
END
-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexCol umns
(
@table_name SYSNAME,
@object_id INT,
@index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@colnames NVARCHAR(4000),
@thisColID INT,
@thisColName SYSNAME
SET @colnames = INDEX_COL(@tabl e_name, @index_id, 1)
+ dbo.GetIndexCol umnOrder(@objec t_id, @index_id, 1)
SET @thisColID = 2
SET @thisColName = INDEX_COL(@tabl e_name, @index_id, @thisColID)
+ dbo.GetIndexCol umnOrder(@objec t_id, @index_id, @thisColID)
WHILE (@thisColName IS NOT NULL)
BEGIN
SET @thisColID = @thisColID + 1
SET @colnames = @colnames + ', ' + @thisColName
SET @thisColName = INDEX_COL(@tabl e_name, @index_id,
@thisColID)
+ dbo.GetIndexCol umnOrder(@objec t_id, @index_id,
@thisColID)
END
RETURN @colNames
END
CREATE VIEW dbo.vAllIndexes
AS
begin
SELECT
TABLE_NAME = OBJECT_NAME(i.i d),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexCol umns(OBJECT_NAM E(i.id), i.id,
i.indid),
IS_CLUSTERED = INDEXPROPERTY(i .id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i .id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS :
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY( i.id, 'IsMsShipped') = 0
end
*/
SELECT
v.*
FROM
dbo.vAllIndexes v
INNER JOIN
INFORMATION_SCH EMA.TABLE_CONST RAINTS T
ON
T.CONSTRAINT_NA ME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TY PE = 'PRIMARY KEY'
AND v.COLUMN_LIST = 'Id'
AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAME
Comment