|
|
Posted by pb648174 on 06/12/84 11:55
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.GetIndexColumnOrder
(
@object_id INT,
@index_id TINYINT,
@column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @r NVARCHAR(5)
SELECT @r = CASE INDEXKEY_PROPERTY
(
@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.GetIndexColumns
(
@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(@table_name, @index_id, 1)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, 1)
SET @thisColID = 2
SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
WHILE (@thisColName IS NOT NULL)
BEGIN
SET @thisColID = @thisColID + 1
SET @colnames = @colnames + ', ' + @thisColName
SET @thisColName = INDEX_COL(@table_name, @index_id,
@thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id,
@thisColID)
END
RETURN @colNames
END
CREATE VIEW dbo.vAllIndexes
AS
begin
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(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_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND v.COLUMN_LIST = 'Id'
AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAME
Navigation:
[Reply to this message]
|