|
Its still a cool tool though - saved me hours and a lot of grief
|
|
|
|
|
Hi there!
This looks like an awesome utility but we are running into problems when running it against the databases we need to switch from case-insensitive to case-sensitive collation.
When trying to generate the script, the following error appears over and over again:
Cannot insert the value NULL into column 'index_keys', table 'tempdb.dbo#spintab______________________00000000062C' column does not allow nulls. INSERT fails.
This happens against both of our databases. But running against the Northwind database the script is generated without errors and it runs fine.
One note of interest is that the generated script for the Northwind database is properly formatted and contains GO, commit and rollback statements. On the other hand, the script produced against our databases did not have any of this...It looks like it made it halfway through the process and then just died.
Any ideas?
Thank you for your time.
-Joe
|
|
|
|
|
I am encountering the same error with our DB. Anyone have any ideas why this is happening, or if there is a fix?
|
|
|
|
|
Hi, sorry about the delay in getting back to you but i did not get a notification message from the code project letting me know that you had posted a message.
I have now resolved the issue and posted an updated version of the collation change to the code project. They should have it available for you to download in the next few days
Alex Baker
|
|
|
|
|
Your script seems to work very well thanks. However it doesn't appear to update VIEWs, is this correct or am I missing something?
Nigel Rickerby
|
|
|
|
|
Nigel,
I do not think regular views need recreating with a collation order as they take the collation order used the columns they bind to. Are you refering to indexed views?
I will admit to having no experience of using indexed views so have not tested this tool with them. If you find that there is a problem and it relates to indexed views then let me know and i will make the necessary modifications.
Alex Bake
|
|
|
|
|
No the issue is with standard views, however I have found the solution. It appears that the syscolumns table continues to report the columns as their old collation until they are recompiled. I simply ran an ALTER on each view (changing nothing) and the issue is corrected. You might want to add running an ALTER statement for each view to your script.
The following select shows any column collations that have NOT been converted to (in this case) Latin1_General_CI_AS, after you program has finished executing.
select object_name(id), name, collation from syscolumns where collation IS NOT NULL AND collation <> N'Latin1_General_CI_AS' order by object_name(id), name
Thanks
Nigel
Nigel Rickerby
|
|
|
|
|
I've been trying to do something like this myself, but never could find the time.
This solved several burning issues with my databases.
Just wanted to say thank you.
Cheers!
|
|
|
|
|
You solved my problem with one click. Thank you
|
|
|
|
|
Hi,
Why does your script drop foreign keys for all int columns? Is it necessary to do that? Isn’t it enough to drop the FK if it’s a column that has collation?
|
|
|
|
|
When dropping a primary key you need to drop all foreign keys that relate to it as well - i decided that it was too much effort to work out what needs dropping and what can stay. Since you have made the comment i have modified the script to only drop the foreign keys and indexes that really need dropping.
I still drop all check constraints, table functions and calculated columns.
I have tested on a few of my databases but would appreciate if you could also check the change on one of your databases.
Note that you did make me review the code and i did find a problem where indexes with columns sorted in descending order would be recreated in ascending order.
Alex
|
|
|
|
|
Actually i have not updated the code yet - i need to submit it and get the code project to change it. This may take a few days.
Alex
|
|
|
|
|
Just thought that I'd like to thank you for posting such a useful bit of code. I don't need to use for the purpose you intended - but it is really useful to find a bit of code that creates a script to drop all indexes, constraints etc - and rebuild them again.
You have saved me a load of typing - thank you!
|
|
|
|
|
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>
|
|
|
|
|
What is the problem? The script file is a resource used by the program - You do not run the script file yourself - you run the program which uses the script as a template substituting database name and collation order as required.
When the program runs it will execute the script with the appropriate substitutions - in doing so a new script will be generated for your chosen database - you will need to copy and paste this in to the SQL Server query analyser if you want to run it manually or if you want the program to run the script for you then press the go button.
If you find that the generated script causes errors, looses database integrity or data then please supply some more details and i will see if i can fix it.
Alex Baker
|
|
|
|
|
Well it depends on your database setting I guess but for me,
I had to fix the capitalization of parameters, aliases and tablenames.
ex:
@Colname <> @ColName, @IX_objid <> @IX_ObjID
join syscolumns c on O.ID = C.id
colID, ColID <> colid (sysconstraints table)
Parameters:
@CC_typename
@Colname
@IX_objid
@IX_objname
Table aliases/prefixes:
C <> c
O <> o
SC <> sc
T <> t
Column names:
ColID, colID <> colid
ConstID, constID <> constid
fKeyID <> fkeyid
Name <> name
ID <> id
IsNullable <> isnullable
rkeyID, rKeyID <> rkeyid
TEXT <> text
SysComments <> syscomments
|
|
|
|
|
Ah yes - i have never worked on a database with a case sensitive collation order before. I guess that this makes the SQL code case sensitive as well.
I will update my posting in the next few days to contain a modified script.
Many thanks for bringing this to my attention.
Alex Baker
|
|
|
|
|
I just ran into the same issue and was considering either fixing up the script by hand or fixing it in the source. Did you ever get around to doing the work? If so, could you post the fix? This looks to be a fantastic utility!
CK
|
|
|
|
|