jeudi 8 janvier 2015

Script for dropping keys, contraints, index... working all but one case


I have these two SQL Server 2008 R2 scripts:



DECLARE @sql12 NVARCHAR(MAX) = N'';
SELECT @sql12 += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) +
' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.key_constraints
WHERE parent_object_id = object_id('TabAutisti');
PRINT @sql12;
-- EXEC sp_executesql @sql12;

PRINT '@@@'

DECLARE @sql13 NVARCHAR(MAX) = N'';
SELECT @sql13 += N'
DROP INDEX ' + QUOTENAME(sys.indexes.name) +
'ON TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(sys.indexes.object_id))
+ '.' + QUOTENAME(OBJECT_NAME(sys.indexes.object_id)) + ';'
FROM sys.indexes
-- WHERE object_id = object_id('TabAutisti');
PRINT @sql13;
-- EXEC sp_executesql @sql13;


The first works, but the second not.


If I simply write:



SELECT @sql13 += N'
DROP INDEX something';'


it prints correctly the strings... so, there is something wrong here:



SELECT @sql13 += N'
DROP INDEX ' + QUOTENAME(sys.indexes.name) +
'ON TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(sys.indexes.object_id))
+ '.' + QUOTENAME(OBJECT_NAME(sys.indexes.object_id)) + ';'


But I cannot find errors. Do you have any ideas?





Aucun commentaire:

Enregistrer un commentaire