November 6, 2014 at 5:32 am
Hi,
i have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this.
some of primary key columns are used in references in other tables.
is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.
DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512),
@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX),
@column NVARCHAR(MAX);
DECLARE @indexname NVARCHAR(512);
SELECT name As 'Table'
INTO #Indexes
FROM sys.tables
WHERE name like 'Sales'
WHILE (SELECT COUNT(*) FROM #Indexes) > 0
BEGIN
SET @table = (SELECT TOP 1 [Table] FROM #Indexes)
SELECT c.name,
i.name,
fk.name as fk_name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON i.object_id = t.object_id
INNER JOIN sys.index_columns ic ON ic.object_id = t.object_id
INNER JOIN sys.columns c ON c.object_id = t.object_id
AND ic.column_id = c.column_id
LEFT JOIN sys.key_constraints FK ON fk.object_id =t.object_id
WHERE i.is_primary_key = 1
AND t.name = @table;
SET @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @indexname
SET @sql2 = 'DROP INDEX ' + @indexname + ' ON ' + @table
SET @sql3 ='ALTER TABLE ' + @table + ' ADD CONSTRAINT ' + @indexname+ ' PRIMARY KEY CLUSTERED(' + @column +')'
Print (@sql);
print (@sql2);
print (@sql3);
DELETE FROM #Indexes WHERE [Table] = @table;
END
DROP TABLE #Indexes
November 6, 2014 at 7:33 am
Duplicate post.
Replies here please:
http://www.sqlservercentral.com/Forums/FindPost1631386.aspx
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply