November 6, 2014 at 6:27 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:19 am
OK, I've messed around with this over lunch and am reasonably confident that it does what you want. Please be extremely careful, execute in a test environment that is the same as the production environment first if you can.
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = STUFF((SELECT CHAR(13)+CHAR(10)+[drops]+[creates]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY OBJECT_NAME(pkey.tableID)),
ISNULL(fkey.dropConst + CHAR(13) + CHAR(10),''),
'IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+CHAR(39)+OBJECT_NAME(pkey.tableID)+CHAR(39)+
') AND name = N'+CHAR(39)+pkey.constraintName+CHAR(39)+')'+CHAR(13)+CHAR(10)+ 'BEGIN'+CHAR(13)+CHAR(10)+
'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(pkey.tableID)) + '.' + QUOTENAME(OBJECT_NAME(pkey.tableID))+
' DROP CONSTRAINT '+QUOTENAME(pkey.constraintName)+';'+CHAR(13)+CHAR(10)+ 'END;'+CHAR(13)+CHAR(10)+
'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(pkey.tableID)) + '.' + QUOTENAME(OBJECT_NAME(tableID)) + ' ADD CONSTRAINT ' +
+QUOTENAME(pkey.constraintName)+' PRIMARY KEY CLUSTERED ( ' + pkey.name + ' );' +
ISNULL(CHAR(13) + CHAR(10) + fkey.createConst,'')
FROM (SELECT DISTINCT b.tableID, b.constraintName,
STUFF((SELECT ',' + QUOTENAME(name)
FROM(SELECT col.name, ind.tableID
FROM (SELECT object_id
FROM sys.indexes
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
) ind(tableID)
INNER JOIN sys.key_constraints const ON ind.tableID = const.parent_object_id
INNER JOIN sys.index_columns indCol ON indCol.object_id = ind.tableID AND
indCol.index_id = const.unique_index_id
INNER JOIN sys.columns col ON col.object_id = ind.tableID AND col.column_id = indCol.column_id
WHERE const.type = 'PK') a
WHERE a.tableID = b.tableID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'') AS name
FROM(SELECT col.name, ind.tableID, const.name AS constraintName
FROM (SELECT object_id
FROM sys.indexes
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
) ind(tableID)
INNER JOIN sys.key_constraints const ON ind.tableID = const.parent_object_id
INNER JOIN sys.index_columns indCol ON indCol.object_id = ind.tableID AND
indCol.index_id = const.unique_index_id
INNER JOIN sys.columns col ON col.object_id = ind.tableID AND col.column_id = indCol.column_id
WHERE const.type = 'PK'
) b
) pkey
LEFT OUTER JOIN (SELECT DISTINCT c.referenced_object_id,
STUFF((SELECT ISNULL(CHAR(13)+CHAR(10)+
'IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'+
CHAR(39)+a.constraintName+CHAR(39)+') AND parent_object_id = OBJECT_ID(N'+CHAR(39)+
a.tableReference+CHAR(39)+'))' + CHAR(13) + CHAR(10) + 'BEGIN' + CHAR(13) + CHAR(10) +
'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(a.parent_object_id))+'.'+QUOTENAME(a.tableReference)+
' DROP CONSTRAINT ' + a.constraintName + ';' + CHAR(13) + CHAR(10) + 'END;','')
FROM (SELECT OBJECT_NAME(fk.constraint_object_id) AS constraintName,
OBJECT_NAME(fk.parent_object_id) AS tableReference,
fk.parent_object_id, fk.referenced_object_id, fcol.name AS foreignColumn,
col.name AS ourColumn
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns fcol ON fcol.object_id = fk.referenced_object_id AND
fcol.column_id = fk.referenced_column_id
INNER JOIN sys.columns col ON col.object_id = fk.parent_object_id AND
col.column_id = fk.parent_column_id
WHERE fk.referenced_object_id = c.referenced_object_id
) a
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,2,'') AS dropConst,
STUFF((SELECT ISNULL(CHAR(13) + CHAR(10) + 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(b.parent_object_id))+
'.'+QUOTENAME(b.tableReference) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(b.constraintName) +
' FOREIGN KEY(' + b.ourColumn + ')' + CHAR(13) + CHAR(10) + 'REFERENCES ' +
QUOTENAME(OBJECT_SCHEMA_NAME(b.referenced_object_id)) + '.' +
QUOTENAME(OBJECT_NAME(b.referenced_object_id)) + ' (' + b.foreignColumn + ')' + CHAR(13) + CHAR(10) +
CASE WHEN delete_referential_action_desc = 'CASCADE' THEN 'ON DELETE CASCADE' + CHAR(13) + CHAR(10) ELSE '' END +
CASE WHEN update_referential_action_desc = 'CASCADE' THEN 'ON UPDATE CASCADE' + CHAR(13) + CHAR(10) ELSE '' END +
'NOT FOR REPLICATION;' + CHAR(13) + CHAR(10) + 'ALTER TABLE ' +
QUOTENAME(OBJECT_SCHEMA_NAME(b.parent_object_id))+'.'+QUOTENAME(b.tableReference) +
' CHECK CONSTRAINT '+ QUOTENAME(b.constraintName)+';','')
FROM (SELECT OBJECT_NAME(fk.constraint_object_id) AS constraintName,
OBJECT_NAME(fk.parent_object_id) AS tableReference,
fk.parent_object_id, fk.referenced_object_id, fcol.name AS foreignColumn,
col.name AS ourColumn, delete_referential_action_desc, update_referential_action_desc
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns fcol ON fcol.object_id = fk.referenced_object_id AND
fcol.column_id = fk.referenced_column_id
INNER JOIN sys.columns col ON col.object_id = fk.parent_object_id AND
col.column_id = fk.parent_column_id
INNER JOIN sys.foreign_keys f ON fk.constraint_object_id = f.object_id
WHERE fk.referenced_object_id = c.referenced_object_id
) b
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,2,'') AS createConst
FROM (SELECT OBJECT_NAME(fk.constraint_object_id) AS constraintName,
OBJECT_NAME(fk.parent_object_id) AS tableReference,
fk.parent_object_id, fk.referenced_object_id, fcol.name AS foreignColumn,
col.name AS ourColumn
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns fcol ON fcol.object_id = fk.referenced_object_id AND
fcol.column_id = fk.referenced_column_id
INNER JOIN sys.columns col ON col.object_id = fk.parent_object_id AND
col.column_id = fk.parent_column_id
) c
) fkey ON fkey.referenced_object_id = pkey.tableID
)pieces([pos], [drops], [creates])
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,2,'');
--== DISPLAY AS XML SO THAT SQL SERVER DOESN'T CHOP IT UP
SELECT @SQL FOR XML PATH('');
--EXECUTE sp_executesql @SQL;
November 6, 2014 at 7:21 am
You'd better find more reliable source for scripts. Check this site scripts library
http://www.sqlservercentral.com/search/?q=index+recreate&t=s&sort=relevance
November 6, 2014 at 7:37 am
Thank for the script, is there anyway i can run one table at a time ? i mean a where condition with table name?
November 6, 2014 at 7:47 am
Shahzadi (11/6/2014)
Thank for the script, is there anyway i can run one table at a time ? i mean a where condition with table name?
Of course you can limit it to a particular table if you choose.
DECLARE @SQL NVARCHAR(MAX), @TABLE VARCHAR(250) = '';
SELECT @SQL = STUFF((SELECT CHAR(13)+CHAR(10)+[drops]+[creates]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY OBJECT_NAME(pkey.tableID)),
ISNULL(fkey.dropConst + CHAR(13) + CHAR(10),''),
'IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+CHAR(39)+OBJECT_NAME(pkey.tableID)+CHAR(39)+
') AND name = N'+CHAR(39)+pkey.constraintName+CHAR(39)+')'+CHAR(13)+CHAR(10)+ 'BEGIN'+CHAR(13)+CHAR(10)+
'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(pkey.tableID)) + '.' + QUOTENAME(OBJECT_NAME(pkey.tableID))+
' DROP CONSTRAINT '+QUOTENAME(pkey.constraintName)+';'+CHAR(13)+CHAR(10)+ 'END;'+CHAR(13)+CHAR(10)+
'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(pkey.tableID)) + '.' + QUOTENAME(OBJECT_NAME(tableID)) + ' ADD CONSTRAINT ' +
+QUOTENAME(pkey.constraintName)+' PRIMARY KEY CLUSTERED ( ' + pkey.name + ' );' +
ISNULL(CHAR(13) + CHAR(10) + fkey.createConst,'')
FROM (SELECT DISTINCT b.tableID, b.constraintName,
STUFF((SELECT ',' + QUOTENAME(name)
FROM(SELECT col.name, ind.tableID
FROM (SELECT object_id
FROM sys.indexes
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
AND OBJECT_NAME(object_id) = @TABLE
) ind(tableID)
INNER JOIN sys.key_constraints const ON ind.tableID = const.parent_object_id
INNER JOIN sys.index_columns indCol ON indCol.object_id = ind.tableID AND
indCol.index_id = const.unique_index_id
INNER JOIN sys.columns col ON col.object_id = ind.tableID AND col.column_id = indCol.column_id
WHERE const.type = 'PK') a
WHERE a.tableID = b.tableID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'') AS name
FROM(SELECT col.name, ind.tableID, const.name AS constraintName
FROM (SELECT object_id
FROM sys.indexes
WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
) ind(tableID)
INNER JOIN sys.key_constraints const ON ind.tableID = const.parent_object_id
INNER JOIN sys.index_columns indCol ON indCol.object_id = ind.tableID AND
indCol.index_id = const.unique_index_id
INNER JOIN sys.columns col ON col.object_id = ind.tableID AND col.column_id = indCol.column_id
WHERE const.type = 'PK'
) b
) pkey
LEFT OUTER JOIN (SELECT DISTINCT c.referenced_object_id,
STUFF((SELECT ISNULL(CHAR(13)+CHAR(10)+
'IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'+
CHAR(39)+a.constraintName+CHAR(39)+') AND parent_object_id = OBJECT_ID(N'+CHAR(39)+
a.tableReference+CHAR(39)+'))' + CHAR(13) + CHAR(10) + 'BEGIN' + CHAR(13) + CHAR(10) +
'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(a.parent_object_id))+'.'+QUOTENAME(a.tableReference)+
' DROP CONSTRAINT ' + a.constraintName + ';' + CHAR(13) + CHAR(10) + 'END;','')
FROM (SELECT OBJECT_NAME(fk.constraint_object_id) AS constraintName,
OBJECT_NAME(fk.parent_object_id) AS tableReference,
fk.parent_object_id, fk.referenced_object_id, fcol.name AS foreignColumn,
col.name AS ourColumn
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns fcol ON fcol.object_id = fk.referenced_object_id AND
fcol.column_id = fk.referenced_column_id
INNER JOIN sys.columns col ON col.object_id = fk.parent_object_id AND
col.column_id = fk.parent_column_id
WHERE fk.referenced_object_id = c.referenced_object_id
) a
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,2,'') AS dropConst,
STUFF((SELECT ISNULL(CHAR(13) + CHAR(10) + 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(b.parent_object_id))+
'.'+QUOTENAME(b.tableReference) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(b.constraintName) +
' FOREIGN KEY(' + b.ourColumn + ')' + CHAR(13) + CHAR(10) + 'REFERENCES ' +
QUOTENAME(OBJECT_SCHEMA_NAME(b.referenced_object_id)) + '.' +
QUOTENAME(OBJECT_NAME(b.referenced_object_id)) + ' (' + b.foreignColumn + ')' + CHAR(13) + CHAR(10) +
CASE WHEN delete_referential_action_desc = 'CASCADE' THEN 'ON DELETE CASCADE' + CHAR(13) + CHAR(10) ELSE '' END +
CASE WHEN update_referential_action_desc = 'CASCADE' THEN 'ON UPDATE CASCADE' + CHAR(13) + CHAR(10) ELSE '' END +
'NOT FOR REPLICATION;' + CHAR(13) + CHAR(10) + 'ALTER TABLE ' +
QUOTENAME(OBJECT_SCHEMA_NAME(b.parent_object_id))+'.'+QUOTENAME(b.tableReference) +
' CHECK CONSTRAINT '+ QUOTENAME(b.constraintName)+';','')
FROM (SELECT OBJECT_NAME(fk.constraint_object_id) AS constraintName,
OBJECT_NAME(fk.parent_object_id) AS tableReference,
fk.parent_object_id, fk.referenced_object_id, fcol.name AS foreignColumn,
col.name AS ourColumn, delete_referential_action_desc, update_referential_action_desc
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns fcol ON fcol.object_id = fk.referenced_object_id AND
fcol.column_id = fk.referenced_column_id
INNER JOIN sys.columns col ON col.object_id = fk.parent_object_id AND
col.column_id = fk.parent_column_id
INNER JOIN sys.foreign_keys f ON fk.constraint_object_id = f.object_id
WHERE fk.referenced_object_id = c.referenced_object_id
) b
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,2,'') AS createConst
FROM (SELECT OBJECT_NAME(fk.constraint_object_id) AS constraintName,
OBJECT_NAME(fk.parent_object_id) AS tableReference,
fk.parent_object_id, fk.referenced_object_id, fcol.name AS foreignColumn,
col.name AS ourColumn
FROM sys.foreign_key_columns fk
INNER JOIN sys.columns fcol ON fcol.object_id = fk.referenced_object_id AND
fcol.column_id = fk.referenced_column_id
INNER JOIN sys.columns col ON col.object_id = fk.parent_object_id AND
col.column_id = fk.parent_column_id
) c
) fkey ON fkey.referenced_object_id = pkey.tableID
)pieces([pos], [drops], [creates])
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,2,'');
--== DISPLAY AS XML SO THAT SQL SERVER DOESN'T CHOP IT UP
SELECT @SQL FOR XML PATH('');
--EXECUTE sp_executesql @SQL;
November 6, 2014 at 7:53 am
Oh, you are star, that's exactly what i wanted.. :).. one last thing, actually two 🙂 ... it is showing following characters end of each line & # x 0 D .. and also results seems to be truncate at the end, do you think it could be size of @SQL ?
November 6, 2014 at 7:59 am
Shahzadi (11/6/2014)
Oh, you are star, that's exactly what i wanted.. :).. one last thing, actually two 🙂 ... it is showing following characters end of each line & # x 0 D .. and also results seems to be truncate at the end, do you think it could be size of @SQL ?
"& # x 0 D", with no spaces, is the XML character for carriage return.
Did you execute and view the results as text instead of grid? I returned it as XML because SQL Server cuts off if you try to view it as a text. Return it to grid, then click the blue XML, then copy and paste in to a new window. Otherwise, just remove the comment from the execution line (e.g. change "--EXECUTE sp_executesql @SQL;" to "EXECUTE sp_executesql @SQL;").
November 6, 2014 at 8:08 am
I was returning result into grid but when i was clicking on blue xml it was throwing and error
Unable to show XML. The following error happenend.
Unexpteced end of file has occurred. Line 37, Position 93.
One solution is to increase the number of characters retrieved from server for XML data. To change this setting, on the menu, Click Optioin.
Even though i have changed the option to return unlimited data in XML and open a new query window but still getting error, do you think i should close SSMS and open again ?
November 6, 2014 at 8:30 am
Shahzadi (11/6/2014)
I was returning result into grid but when i was clicking on blue xml it was throwing and errorUnable to show XML. The following error happenend.
Unexpteced end of file has occurred. Line 37, Position 93.
One solution is to increase the number of characters retrieved from server for XML data. To change this setting, on the menu, Click Optioin.
Even though i have changed the option to return unlimited data in XML and open a new query window but still getting error, do you think i should close SSMS and open again ?
Might work. Sounds like a memory thing, I'm guessing that your management studio has been open for a while or that your system doesn't have a whole lot of memory?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply