|
Needed some fixes in the Script.sql file for it to work for me.
<code>USE [{0}]
DECLARE @textptr binary(16)
DECLARE @SQLSegment nvarchar(4000)
DECLARE @C Cursor
SET nocount ON
create TABLE #SQL (id int primary key identity(1,1),SQL nText)
INSERT INTO #SQL (SQL) VALUES ('ALTER database [{0}] SET single_user')
INSERT INTO #SQL (SQL) VALUES ('USE [{0}]')
INSERT INTO #SQL (SQL)
SELECT 'ALTER TABLE ['+ object_Name(id) + '] DROP CONSTRAINT [' + object_name(constid) + ']'
FROM sysconstraints C
WHERE objectproperty(constid,'IsForeignKey')=1
/*script out dropping of check constraints */
INSERT INTO #SQL (SQL)
SELECT 'ALTER TABLE [' + Object_Name(CS.id) + '] DROP CONSTRAINT ['+object_name(CS.constid)+']'
FROM sysconstraints CS
WHERE objectproperty(CS.constid,'IsCheckCnst') = 1
/*DROP calculated columns*/
INSERT INTO #SQL (SQL)
SELECT 'ALTER TABLE ['+ object_name(id)+ '] DROP COLUMN ['+name+']'
FROM syscolumns
WHERE iscomputed=1
AND objectproperty(id,'IsMSShipped')=0
AND objectproperty(id,'IsTable')=1
--DROP TABLE Functions
INSERT INTO #SQL (SQL)
SELECT 'DROP FUNCTION ['+ name+ ']'
FROM sysobjects
WHERE objectproperty(id,'IsMSShipped')=0
AND objectproperty(id,'IsTableFunction')=1
-- script DROP of indexes
INSERT INTO #SQL (SQL)
SELECT CASE WHEN (status & 4096)<>0 or (status & 2048) <> 0 THEN
'ALTER TABLE [' + object_name(id) +'] DROP CONSTRAINT ['+name+']'
--CONSTRAINT
ELSE
--index
'DROP INDEX [' + object_name(id) +'].['+name+']'
END
FROM sysindexes
WHERE /*id = @IX_ObjID AND */indid > 0 AND indid < 255 AND (status & 64)=0
AND objectproperty(id,'ISMSSHIPPED')=0
ORDER BY object_name(id),indid
-- script the changing of the database collation
INSERT INTO #SQL (SQL) VALUES ('USE [MASTER]')
INSERT INTO #SQL (SQL) VALUES ('ALTER database [{0}] COLLATE {1}')
INSERT INTO #SQL (SQL) VALUES ('USE [{0}]')
-- script out the changing of column level collation
DECLARE @CC_TableName sysname,
@ColName sysname,
@CC_Length nvarchar(100),
@CC_TypeName sysname,
@CC_OtherText nvarchar(4000),
@CC_NullText nvarchar(100)
SET @C = CURSOR FOR
SELECT O.name AS tablename,
C.name AS colname,
CASE WHEN T.name like 'n%' THEN CAST(C.length / 2 AS nvarchar(100)) ELSE CAST(C.length AS nvarchar(100)) END AS Length,
T.name AS typename,
CASE WHEN C.isnullable=1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
JOIN systypes T
ON T.xtype = C.xtype
AND T.xusertype = C.xusertype
WHERE O.type ='U'
AND objectproperty(O.id,'IsMSShipped')=0
AND C.collationid IS NOT NULL
--AND C.collation <> CAST(DATABASEPROPERTYEX(DB_NAME(),'collation') AS sysname)
OPEN @C
FETCH NEXT FROM @C INTO @CC_TableName, @ColName, @CC_Length, @CC_TypeName,@CC_NullText
WHILE @@Fetch_Status = 0
BEGIN
IF @CC_TypeName COLLATE DATABASE_DEFAULT in ('ntext','text')
BEGIN
-- we can not use the ALTER TABLE statment to change column level collation ON text columns
--we need to do each of these AS a separate transaction dur to the risks of errors
SET @SQLSegment = '
DECLARE @InError bit
SET @InError =0
BEGIN transaction
-- add a temp column
EXEC (''ALTER TABLE ['+@CC_TableName+'] add [____temp] [' + @CC_TypeName + ']'')
-- copy data to temp column
IF @@error<>0 SET @InError =1
IF @@error = 0
EXEC (''UPDATE ['+@CC_TableName+'] SET [____temp] =[' + @ColName + ']'')
-- readd origional column
IF @@error<>0 SET @InError =1
IF @@error = 0'
-- IF default CONSTRAINT must add it
IF EXISTS ( SELECT *
FROM sysconstraints
WHERE id = object_ID(@CC_TableName)
AND col_name(id,colid) = @ColName
AND (status & 5) = 5 )
-- IF there are default constraints add a bit to do that
SET @SQLSegment = @SQLSegment +
(SELECT '
EXEC (''ALTER TABLE ['+@CC_TableName+'] DROP CONSTRAINT [' + object_name(C.constid) + ']'')
IF @@error<>0 SET @InError =1
IF @@error = 0
EXEC (''ALTER TABLE ['+@CC_TableName+'] DROP COLUMN [' + @ColName + ']'')
IF @@error<>0 SET @InError =1
IF @@error = 0
EXEC ('' ALTER TABLE ['+ object_name(C.id) + '] ADD ['+O.name+'] [' + @CC_TypeName + '] CONSTRAINT [' + object_name(C.constid) + '] DEFAULT ' + replace(T.text,'''','''''') + ' '')'
FROM sysconstraints C
JOIN syscolumns O
ON C.id = O.id
AND C.colid = O.colid
JOIN syscomments T
ON T.id = C.constid
WHERE C.id = object_id(@CC_TableName)
AND (C.status & 5) = 5
AND col_name(C.id,C.colid) = @ColName) --default CONSTRAINT
ELSE
SET @SQLSegment = @SQLSegment + '
EXEC (''ALTER TABLE ['+@CC_TableName+'] DROP COLUMN [' + @ColName + ']'')
-- DROP origional column
IF @@error<>0 SET @InError =1
IF @@error = 0
EXEC (''ALTER TABLE ['+@CC_TableName+'] add [' + @ColName+'] [' + @CC_TypeName + '] '')'
SET @SQLSegment = @SQLSegment + '
-- Copy data back to origional column
IF @@error<>0 SET @InError =1
IF @@error = 0
EXEC (''UPDATE ['+@CC_TableName+'] SET [' + @ColName + '] = [____temp] '')
-- DROP temp column
IF @@error<>0 SET @InError =1
IF @@error = 0
EXEC (''ALTER TABLE ['+@CC_TableName+'] DROP COLUMN [____temp]'')
'
IF @CC_TypeName = 'NOT NULL'
SET @SQLSegment = @SQLSegment + '
-- now SET the appropriate nullability
IF @@error<>0 SET @InError =1
IF @@error = 0
EXEC (''ALTER TABLE ['+@CC_TableName+'] ALTER column [' + @ColName+'] [' + @CC_TypeName + '] '+@CC_NullText+' '')'
SET @SQLSegment = @SQLSegment + '
IF @@error<>0 SET @InError =1
IF @@error = 0
commit transaction
ELSE
rollback transaction
'
INSERT INTO #SQL VALUES (@SQLSegment)
END
ELSE
BEGIN
-- normal columns
SET @SQLSegment = 'ALTER TABLE ['+@CC_TableName COLLATE DATABASE_DEFAULT+'] ALTER Column ['+@ColName COLLATE DATABASE_DEFAULT+ '] ['+@CC_TypeName COLLATE DATABASE_DEFAULT+']'
IF @CC_TypeName COLLATE DATABASE_DEFAULT in ('nVarchar', 'varchar','char','nchar')
SET @SQLSegment = @SQLSegment COLLATE DATABASE_DEFAULT +' ('+@CC_Length COLLATE DATABASE_DEFAULT + ')'
SET @SQLSegment = @SQLSegment COLLATE DATABASE_DEFAULT + ' COLLATE DATABASE_DEFAULT ' + @CC_NullText COLLATE DATABASE_DEFAULT + '
'
INSERT INTO #SQL VALUES (@SQLSegment)
END
FETCH NEXT FROM @C INTO @CC_TableName, @ColName, @CC_Length, @CC_TypeName,@CC_NullText
END
CLOSE @C
DEALLOCATE @C
-- script out recreation of check constraints
INSERT INTO #SQL
SELECT 'ALTER TABLE [' + Object_Name(CS.id) + '] WITH NOCHECK ADD CONSTRAINT ['+object_name(CS.constid)+'] CHECK '+SC.text + '
' + CASE WHEN objectproperty(CS.constid,'CnstIsDisabled') = 1 THEN 'ALTER TABLE [' + Object_Name(CS.id) + '] NOCHECK CONSTRAINT ['+object_name(CS.constid)+']' ELSE '' END
FROM sysconstraints CS
JOIN syscomments SC
ON SC.id = CS.constid
WHERE objectproperty(CS.constid,'IsCheckCnst') = 1
--script out recreation of calculated columns
INSERT INTO #SQL
SELECT 'ALTER TABLE ['+ object_name(C.id)+ '] ADD ['+name+'] AS '+SC.text
FROM syscolumns C
JOIN syscomments SC
ON C.id = SC.id
AND C.colid = SC.number
WHERE C.iscomputed=1
AND objectproperty(C.id,'IsMSShipped')=0
AND objectproperty(C.id,'IsTable')=1
-- script out the recreation of indexes
-- create temp TABLE
create TABLE #spindtab
(
objectname sysname COLLATE DATABASE_DEFAULT NOT NULL,
index_name sysname COLLATE DATABASE_DEFAULT NOT NULL,
stats int,
groupname sysname COLLATE DATABASE_DEFAULT NOT NULL,
index_keys nvarchar(3000) COLLATE DATABASE_DEFAULT NOT NULL, -- see @IX_keys above for length descr
OrigFillFactor tinyint
)
--generate SQL to do indexes
DECLARE @IX_indid smallint, -- the index id of an index
@IX_groupid smallint, -- the filegroup id of an index
@IX_indname sysname,
@IX_groupname sysname,
@IX_status int,
@IX_keys nvarchar(3000),
@IX_dbname sysname,
@IX_ObjID int,
@IX_ObjName sysname,
@IX_OrigFillFactor tinyint
-- Check to see the the TABLE EXISTS AND initialize @IX_ObjID.
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
DECLARE ms_crs_ind cursor local static for
SELECT object_name(id), indid, groupid, name, status, OrigFillFactor FROM sysindexes
WHERE /*id = @IX_ObjID AND */indid > 0 AND indid < 255 AND (status & 64)=0
AND objectproperty(id,'ISMSSHIPPED')=0
ORDER BY object_name(id),indid
OPEN ms_crs_ind
FETCH ms_crs_ind INTO @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor
-- Now check out each index, figure out its type AND keys AND
-- save the info in a temporary TABLE that we'll print out at the END.
WHILE @@fetch_status >= 0
BEGIN
-- First we'll figure out what the keys are.
DECLARE @IX_i int, @IX_thiskey nvarchar(133) -- 128+5
SELECT @IX_keys = '[' + index_col(@IX_ObjName, @IX_indid, 1)+']', @IX_i = 2
IF (indexkey_property(@IX_ObjID, @IX_indid, 1, 'isdescending') = 1)
SELECT @IX_keys = @IX_keys + ' DESC'
SELECT @IX_thiskey = '['+index_col(@IX_ObjName, @IX_indid, @IX_i)+']'
IF ((@IX_thiskey IS NOT NULL) AND (indexkey_property(@IX_ObjID, @IX_indid, @IX_i, 'isdescending') = 1))
SELECT @IX_thiskey = @IX_thiskey + ' DESC'
WHILE (@IX_thiskey IS NOT NULL )
BEGIN
SELECT @IX_keys = @IX_keys + ', ' + @IX_thiskey, @IX_i = @IX_i + 1
SELECT @IX_thiskey = '[' + index_col(@IX_ObjName, @IX_indid, @IX_i) + ']'
IF ((@IX_thiskey IS NOT NULL) AND (indexkey_property(@IX_ObjID, @IX_indid, @IX_i, 'isdescending') = 1))
SELECT @IX_thiskey = @IX_thiskey + ' DESC'
END
SELECT @IX_groupname = groupname FROM sysfilegroups WHERE groupid = @IX_groupid
-- INSERT ROW FOR INDEX
INSERT INTO #spindtab VALUES (@IX_ObjName,@IX_indname, @IX_status, @IX_groupname, @IX_keys, @IX_OrigFillFactor)
-- Next index
FETCH ms_crs_ind INTO @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor
END
DEALLOCATE ms_crs_ind
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
DECLARE @IX_empty varchar(1) SELECT @IX_empty = ''
DECLARE @IX_des1 varchar(35), -- 35 matches spt_values
@IX_des2 varchar(35),
@IX_des4 varchar(35),
@IX_des32 varchar(35),
@IX_des64 varchar(35),
@IX_des2048 varchar(35),
@IX_des4096 varchar(35),
@IX_des8388608 varchar(35),
@IX_des16777216 varchar(35)
SELECT @IX_des1 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 1 --ignoor duplicate keys
SELECT @IX_des2 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 2 --unique
SELECT @IX_des4 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 4 --ignoor duplicate rows
SELECT @IX_des32 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 32 --hypothetical
SELECT @IX_des64 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 64 --statistics
SELECT @IX_des2048 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 2048 --primary key
SELECT @IX_des4096 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 4096 --unique key
SELECT @IX_des8388608 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 8388608 --auto create
SELECT @IX_des16777216 = name FROM master.dbo.spt_values WHERE type = 'I' AND number = 16777216 --stats no recompute
-- DISPLAY THE RESULTS
INSERT INTO #SQL
SELECT CASE WHEN (stats & 4096)<>0 or (stats & 2048) <> 0 THEN
--CONSTRAINT
'ALTER TABLE ['+objectname+'] ADD CONSTRAINT ['+index_name+'] '
+ CASE WHEN (stats & 2048)<>0 THEN 'PRIMARY KEY ' ELSE 'UNIQUE ' END
+ CASE WHEN (stats & 16)<>0 THEN 'clustered' ELSE 'nonclustered' END
+ ' ('+index_keys+')'
+ CASE WHEN OrigFillFactor >0 THEN ' WITH FILLFACTOR =' + CAST(OrigFillFactor AS nvarchar(3)) ELSE @IX_empty END
+ ' ON ['+groupname+']
' COLLATE DATABASE_DEFAULT
ELSE
-- index
'CREATE ' + CASE WHEN (stats & 2)<>0 THEN @IX_des2 +' ' ELSE @IX_empty END +CASE WHEN (stats & 16)<>0 THEN 'clustered' ELSE 'nonclustered' END +' INDEX'
+ ' ['+ index_name +'] ON ['+objectname+'] ('+index_keys+')'
+ CASE WHEN OrigFillFactor >0 or (stats & 1) <> 0 or (stats & 16777216) <> 0 THEN ' WITH ' ELSE @IX_empty END
+ CASE WHEN OrigFillFactor >0 THEN 'PAD_INDEX, FILLFACTOR = ' +CAST(OrigFillFactor AS nvarchar(3) ) ELSE @IX_empty END
+ CASE WHEN (stats & 1) <> 0 THEN ', '+ @IX_des1 ELSE @IX_empty END
+ CASE WHEN (stats & 16777216) <> 0 THEN ', '+ @IX_des16777216 ELSE @IX_empty END
+ ' ON ['+groupname+']
'
END
FROM #spindtab
DROP TABLE #spindtab
-- script recreation of foiegn keys
-- script out foreign keys
DECLARE @FK_KeyName sysname,
@FK_TableName sysname,
@FK_ReferencedTable sysname,
@constid int,
@Col1 sysname,
@Col2 sysname,
@ColList1 nvarchar(2000),
@ColList2 nvarchar(2000),
@CnstIsUpdateCascade bit,
@CnstIsNotRepl bit,
@CnstIsDeleteCascade bit,
@CnstIsDisabled bit,
@C2 cursor
SET @C = CURSOR FOR
SELECT object_Name(id) AS TableName,
object_name(constid) AS KeyName,
(SELECT distinct object_name(rkeyid) FROM sysforeignkeys FK WHERE FK.constid = C.constid) AS ReferencedTable,
constid,
objectproperty(constid,'CnstIsUpdateCascade') CnstIsUpdateCascade,
objectproperty(constid,'CnstIsDeleteCascade') CnstIsDeleteCascade,
objectproperty(constid,'CnstIsNotRepl') CnstIsNotRepl,
objectproperty(constid,'CnstIsDisabled') CnstIsDisabled
FROM sysconstraints C
WHERE objectproperty(constid,'IsForeignKey')=1
OPEN @C
FETCH NEXT FROM @C INTO @FK_TableName, @FK_KeyName, @FK_ReferencedTable,@constid, @CnstIsUpdateCascade, @CnstIsDeleteCascade, @CnstIsNotRepl,@CnstIsDisabled
WHILE @@fetch_Status =0
BEGIN
SET @ColList1 = ''
SET @ColList2 = ''
SET @C2 = CURSOR FOR
SELECT FC.name,
RC.name
FROM sysforeignkeys FK
JOIN syscolumns FC
ON FC.colid = FK.fkey
AND FC.id = FK.fkeyid
JOIN syscolumns RC
ON RC.colid = FK.rkey
AND RC.id = FK.rkeyid
WHERE FK.constid = @constid
OPEN @C2
FETCH NEXT FROM @C2 INTO @Col1, @Col2
WHILE @@Fetch_status=0
BEGIN
IF len(@ColList1) > 0
SET @ColList1 = @ColList1 COLLATE DATABASE_DEFAULT+', '
IF len(@ColList2) > 0
SET @ColList2 = @ColList2 COLLATE DATABASE_DEFAULT+', '
SET @ColList1 = @ColList1 COLLATE DATABASE_DEFAULT +'[' + @Col1 COLLATE DATABASE_DEFAULT + ']'
SET @ColList2 = @ColList2 COLLATE DATABASE_DEFAULT +'[' + @Col2 COLLATE DATABASE_DEFAULT + ']'
FETCH NEXT FROM @C2 INTO @Col1, @Col2
END
CLOSE @C2
DEALLOCATE @C2
SET @SQLSegment = 'ALTER TABLE ['+ @FK_TableName COLLATE DATABASE_DEFAULT + '] WITH NOCHECK ADD CONSTRAINT [' + @FK_KeyName COLLATE DATABASE_DEFAULT + '] FOREIGN KEY ('+@ColList1 COLLATE DATABASE_DEFAULT + ') REFERENCES [' + @FK_ReferencedTable COLLATE DATABASE_DEFAULT+'] ('+ @ColList2 COLLATE DATABASE_DEFAULT +')'
IF @CnstIsUpdateCascade =1
SET @SQLSegment =@SQLSegment + ' ON UPDATE CASCADE'
IF @CnstIsDeleteCascade =1
SET @SQLSegment =@SQLSegment + ' ON DELETE CASCADE'
IF @CnstIsNotRepl =1
SET @SQLSegment =@SQLSegment + ' NOT FOR REPLICATION'
SET @SQLSegment = @SQLSegment +'
'
INSERT INTO #SQL VALUES (@SQLSegment)
IF @CnstIsDisabled=1
BEGIN
SET @SQLSegment = 'ALTER TABLE ['+ @FK_TableName + '] NOCHECK CONSTRAINT [' + @FK_KeyName + ']
'
INSERT INTO #SQL VALUES (@SQLSegment)
END
FETCH NEXT FROM @C INTO @FK_TableName, @FK_KeyName, @FK_ReferencedTable,@constid, @CnstIsUpdateCascade, @CnstIsDeleteCascade, @CnstIsNotRepl,@CnstIsDisabled
END
CLOSE @C
DEALLOCATE @C
-- Script out the creation of the TABLE functions last!
--first switch to use the correct database
INSERT INTO #SQL (SQL) VALUES ('')
--get a text pointer
SELECT @textptr = TEXTPTR(SQL) FROM #SQL WHERE id = (SELECT MAX(id) FROM #SQL)
SET @SQLSegment = '
USE [{0}]
'
UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment
DECLARE @FunctionName sysname,
@LastFunctionName sysname
SET @LastFunctionName =''
SET @C = CURSOR FOR
SELECT name AS functionName,
SC.text
FROM sysobjects O
JOIN syscomments SC
ON O.id = SC.id
WHERE objectproperty(O.id,'IsTableFunction') =1
ORDER BY O.name,
SC.colid
OPEN @C
FETCH NEXT FROM @C INTO @FunctionName, @SQLSegment
WHILE @@Fetch_Status=0
BEGIN
IF @FunctionName<>@LastFunctionName
BEGIN
INSERT INTO #SQL (SQL) VALUES ('')
--get a text pointer
SELECT @textptr = TEXTPTR(SQL) FROM #SQL WHERE id = (SELECT MAX(id) FROM #SQL)
SET @LastFunctionName =@FunctionName
END
UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment
FETCH NEXT FROM @C INTO @FunctionName, @SQLSegment
END
CLOSE @C
DEALLOCATE @C
-- finally SET back to multi user access
INSERT INTO #SQL VALUES ('ALTER database [{0}] SET multi_user')
SELECT * FROM #SQL ORDER BY id
RETURN</code>
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
