|
Paul,
Firtsly thanks for your feedback.
I have noted in my article that i have not written code to handle full text indexes. Since you ask i will take a look and put something in for you although this may take me a few days as SQL server 2005 uses a completly different syntax that SQL server 2000.
The varchar(max) issue is a strange one - i only tested using nvarchar and it worked. Open the file script.sql and look for the comment "--nvarchar max functionality for SQL 2005" (line 413 I think). modify the next line to read
if @CC_Length COLLATE DATABASE_DEFAULT in ('0' , '-1' )
Now recompile the code.
Alex Baker
|
|
|
|
|
Alex, thanks for the response; don't worry about FTI just for me I managed to get around it myself.
BTW Good work on the project
Paul
|
|
|
|
|
Alex,
I tried to change the default collation of AdventureWorks DB on SQL Server Enterprise Edition + SP3 + Cumulative Update 4, and I got a lot of errors, even using "Drop All Keys and Constraints" option.
I skimmed the errors. Most of them happen because of no code for dropping and recreating "CHECK" constraints:
"The object 'CK_ProductVendor_StandardPrice' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation."
This CHECK has the next definition:
"ALTER TABLE [Purchasing].[ProductVendor] WITH CHECK ADD CONSTRAINT [CK_ProductVendor_StandardPrice] CHECK (([StandardPrice]>(0.00)))"
GO
It seems stupid that SQL Server requires dropping this constraint even though it does not reference to a text column.
Also, there were several errors because of XML indexes existing of a teabl like this one:
"Could not drop the primary key constraint 'PK_Contact_ContactID' because the table has an XML index."
I will recreate the DB myself using different collation, but you may be interested in these errors.
Thank you,
Vitali
|
|
|
|
|
Great utility when you need it. Very painful process when you have mismatched server/database/column collation. Is it possible to add the capability to set the collation to blank to it effectively picks up the default database collation -- i.e., don't explicitly set the value.
|
|
|
|
|
I am not sure what you are refering to - once the tool has been run the collation of every column in your database will be the database default regardless of the previous collation.
Perhaps you could describe the steps you have been through and the state the database is in after running the tool. I should then have a better idea of what your requirements are and can develop a solution for you.
Alex Baker
|
|
|
|
|
Thanks for the response. What I'm referring to is If you go into design view of an object in Enterprise Manager or Management studio the collation of a column is set to "database default". You can get a column to say this if you delete the collation text in a particular column and save. When you reopen design view it will show "database default". If it is set this way I can change the database collation and the columns will inherit the new collation automatically.
Your tool is helpful to fix a problem we have when a developer mistakenly scripts a database with collate statements and deployed it to a database/server with different collation. It would be nice to not have to go and run your tool each time the database collation changes to keep the database/columns in sync.
A concrete example of what we do is in development we use binary collation to ensure that our code/scripts/procs/etc. will work in a binary collated database which some enterprise apps such as Siebel require. In our test lab we run both binary and non-binary and in production we run non-binary collation since some applications (other than ours) don't work well with binary databases. As a result we often move promote dev - integration - qa - prod and sometimes a developer will have mistakenly set columns explicitly. If the collation changer application could set collate to the "database default" setting it makes this promotion trivial.
Hope this is clear enough.
Thanks.
-John
|
|
|
|
|
you say:
....When you reopen design view it will show "database default". If it is set this way I can change the database collation and the columns will inherit the new collation automatically.
This did not happen in SQL 2000 which is why i wrote this tool in the first place. This may happen in SQL 2005 - i have not tried it - I can see that the documentation on Alter Database [dbname] collate ... is definatly different to that for SQL 2000.
You have requested that I change the collation order of the database columns to database default - this is in fact what the collation changer does - see line 419 of the file script.sql in the source files provided with this sample.
You should find that if you run the tool against your database and select the "script only" option that the script file it generates does an "alter column <whatever> collate database_default" thus setting each column to have the database default collation. For example a section of a generated script file may look like this:
Alter table [dbo].[MeasureGroup] Alter Column [Format] [nvarchar] (5) COLLATE DATABASE_DEFAULT NULL<br />
<br />
Alter table [dbo].[MeasureGroup] Alter Column [Tag] [nvarchar] (100) COLLATE DATABASE_DEFAULT NULL
Hope this helps explain a little.
Alex Baker
|
|
|
|
|
Hi sir,
I run the app and the produced script to change my database into Latin1_General_BIN, the script was successful for there were no errors reported. however, when i check the database properties at SQL server 2000 Ent Manager it remained unchange (Latin1_General_CP1_CI_AS)
Please help.
Thanks.
Master
|
|
|
|
|
SQL Server enterprise manager does cache a lot of stuff - have you tried closing it down and starting it up again?
Alex
|
|
|
|
|
Yes Sir i tried that. i even shutdown my server.
|
|
|
|
|
And you chose the script and execute option not simply the script option?
If you are still experiencing issues have you tried selecting the script only option and copying the script in to query analyzer - does this work?
Alex Baker
|
|
|
|
|
I only chose the script only option. have not tried script and execute. do you recommend that?
*** i tried script and execute just now but i have encountered the issues on "Must Declare Variables" error. though i encountered the same error on "SCRIPT ONLY" option, i changed all variables affected with lowercase. i ran the script at SQL query analyzer it was successful though and has no errors, but still, the collation remains unchange.
-- modified at 20:44 Sunday 1st April, 2007
|
|
|
|
|
Mr. Baker any comment please...
|
|
|
|
|
My appologies for not getting back to you - the code project's e-mail notifications are somewhat spuradic and sometimes i do not get notified of posts.
Could you send me the script as it is generated by the tool - i can then try to identify the issues you are experiencing.
my e-mail is alexb at csl-uk.com
Alex Baker
|
|
|
|
|
I don't think it was worth the any comment ...
|
|
|
|
|
Hey Everyone!!!
Has anyone used this tool with SQL Server 2005??? If so did you hit any problems? I am Trying but maybe i am not giving the tool enough time to build the script because it doesnt seem to finish within about 45 min. Should i give it more time?
"FIRST TIMER" with this blog stuff!!! Thank you in advance!
Runnin_Blind***
Love this STUFF!
|
|
|
|
|
I have not really tested with SQL 2005 yet but it should work - if there are problems then you should get see errors. You will probably find your problem is because the application can not get an exclusive lock on the database.
Start the collection changer tool and then open query analyzer (on the master database) and use the SP_WHO stored procedure to idendify active connections that are open on the database you are trying to change. Use the "kill" statment to kill off the offending connections.
Alex
|
|
|
|
|
Thanks very much for this tool!
well done on sqlserver2000
|
|
|
|
|
I hada similar issue with SQl 2000. But it appeared that I had another script window open in the Query Analyser and connected to the DB I wanted change collation for. So double check in the Enterprise Manager that you don't have any other connections...
BTW, great tool.
|
|
|
|
|
Runnin_Blind***
Love this STUFF!
|
|
|
|
|
You, sir, are a genius. I was just about to start writing a plugin for a code generator I'm using to do just this when I came across your tool. You've saved me several hours of work, and for that I kiss you (err, a virtual kiss on the cheek, eh?).
______________ Marc Heiligers Not one shred of evidence supports the notion that life is serious
|
|
|
|
|
Thanks - flattery will get you a long way.
I have posted an update to the code project that addresses some of the issues mentioned in the posts below and adds some SQL 2005 support. It should be published in a few days
Alex
|
|
|
|
|
Server: Msg 170, Level 15, State 1, Line 78
Line 78: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 296
Line 296: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 304
Line 304: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 308
Line 308: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 384
Line 384: Incorrect syntax near 'COLLATE'.
Server: Msg 156, Level 15, State 1, Line 480
Incorrect syntax near the keyword 'else'.
Server: Msg 170, Level 15, State 1, Line 483
Line 483: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 486
Line 486: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 488
Line 488: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 545
Line 545: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 653
Line 653: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 655
Line 655: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 657
Line 657: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 658
Line 658: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 665
Line 665: Incorrect syntax near 'collate'.
|
|
|
|
|
Could you send a little background? Which version of SQL server are you using - could you send me a copy of the generated script?
Thanks
Alex
|
|
|
|
|
Hi there, thank you for the quick response. here's the background, It is a server migration from SQL 7 to SQL 2000. The database works fine apart from a report function hence the need to change the collation. People are using the database at the moment so I have created a test db from a backup and have attempted to create the script from that.
Thanks
USE [Prism Test]
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 [Prism Test] set single_user')
insert into #SQL (SQL) values ('USE [Prism Test]')
insert into #SQL (SQL)
select 'Alter table ['+ u.name + '].['+o.name+'] DROP CONSTRAINT [' + object_name(constid) + ']'
from sysconstraints c
join sysobjects o
on c.id = o.id
join sysusers u
on u.uid = o.uid
where objectproperty(constid,'IsForeignKey')=1
and constid in (
select fk.constid
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 fc.collationid is not null
or rc.collationid is not null
or 1=1 ) --parameter allows all constraints to be dropped
/*script out dropping of check constraints */
insert into #SQL (SQL)
select 'Alter table ['+ u.name + '].['+o.name+'] drop constraint ['+object_name(cs.constid)+']'
from sysconstraints cs
join sysobjects o
on cs.id = o.id
join sysusers u
on u.uid = o.uid
where objectproperty(cs.constid,'IsCheckCnst') = 1
/*drop calculated columns*/
insert into #SQL (SQL)
select 'ALTER TABLE ['+ u.name + '].['+o.name+'] drop column ['+c.name+']'
from syscolumns c
join sysobjects o
on c.id = o.id
join sysusers u
on u.uid = o.uid
where c.iscomputed=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsTable')=1
--drop Table Functions
insert into #SQL (SQL)
select 'DROP FUNCTION ['+ u.name + '].['+o.name+']'
from sysobjects o
join sysusers u
on o.uid = u.uid
where objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsTableFunction')=1
-- script drop of indexes - we will also populate a temp table that helps recreate the indexes later
create table #spindtab
(
-- owner sysname collate database_default NOT NULL,
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,
IsAutoStatistic bit
)
--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,
@IX_IsAutoStatistic bit
-- 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 i.id, '['+u.name+'].['+o.name+']' as objectname, i.indid, i.groupid, i.name, i.status, i.OrigFillFactor, case when (i.status & 64) = 0 then 0 else isnull(INDEXPROPERTY(i.id,i.name,'IsAutoStatistics'),0) end as IsAutoStatistic from sysindexes i
join sysobjects o
on i.id = o.id
join sysusers u
on u.uid = o.uid
where /*id = @IX_objid and */i.indid > 0 and i.indid < 255
and objectproperty(i.id,'ISMSSHIPPED')=0 and objectproperty(i.id,'IsTableFunction')=0
order by object_name(i.id),i.indid
open ms_crs_ind
fetch ms_crs_ind into @IX_objid, @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor, @IX_IsAutoStatistic
-- 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
declare @rebuild_index bit
select @IX_keys = '[' + index_col(@IX_objname, @IX_indid, 1)+']', @IX_i = 2, @rebuild_index=1 --parameter from application can force all to be rebuilt
if (indexkey_property(@IX_objid, @IX_indid, 1, 'IsDescending') = 1)
set @IX_keys = @IX_keys + ' DESC'
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, 1, 'columnid')) is not null
set @rebuild_index=1
set @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))
set @IX_thiskey = @IX_thiskey + ' DESC'
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
set @rebuild_index=1
while (@IX_thiskey is not null )
begin
select @IX_keys = @IX_keys + ', ' + @IX_thiskey, @IX_i = @IX_i + 1
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
set @rebuild_index=1
set @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
if @rebuild_index =1
insert into #spindtab values ( @IX_ObjName,@IX_indname, @IX_status, @IX_groupname, @IX_keys, @IX_OrigFillFactor, @IX_IsAutoStatistic)
-- Next index
fetch ms_crs_ind into @IX_objid, @IX_ObjName, @IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor, @IX_IsAutoStatistic
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
insert into #SQL
select case when (stats & 4096)<>0 or (stats & 2048) <> 0 then
--Constraint
'ALTER TABLE '+objectname+' DROP CONSTRAINT ['+index_name+'] '
when (stats & 64) <> 0 then
'DROP STATISTICS '+objectname+'.['+index_name+'] '
else
-- index
'DROP INDEX '+objectname+'.['+ index_name +'] '
end
from #spindtab
-- script the changing of the database collation
insert into #SQL (SQL) values ('USE [MASTER]')
insert into #SQL (SQL) values ('alter database [Prism Test] collate Latin1_General_CI_AS')
insert into #SQL (SQL) values ('USE [Prism Test]')
-- Script out the creation of the table functions now as they may be used in triggers that will execute when columns are updated!
--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 [Prism Test]
'
UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment
--recreate functions - make sure owner name is specified!
declare @fn_name sysname,
@fn_id int,
@fn_idlast int,
@fn_last sysname,
@fn_owner sysname,
@fn_testText nvarchar(4000),
@fn_posCreateFn int,
@fn_posCommentStart int,
@fn_posCommentEnd int,
@fn_offset int,
@fn_posFunctionName int,
@fn_posObjectName int
set @fn_idlast =0
set @C = cursor for
select o.name as functionName,
o.id,
sc.text,
u.name
from sysobjects o
join syscomments sc
on o.id = sc.id
join sysusers u
on u.uid = o.uid
where objectproperty(o.id,'IsTableFunction') =1
order by o.id,
sc.colid
open @C
fetch next from @C into @fn_name, @fn_id, @SQLSegment, @fn_owner
while @@Fetch_Status=0
begin
if @fn_id<>@fn_idLast
begin
--add a row for our data
insert into #SQL (SQL) values ('')
--get a text pointer
SELECT @textptr = TEXTPTR(SQL) FROM #SQL where ID = (select max(ID) from #SQL)
set @fn_idLast =@fn_id
-- now look at the create function part of the SQL, make sure that the owner
-- name is specified
set @fn_posCreateFn = patindex('%create%function%', @SQLSegment)
set @fn_posCommentStart = patindex('%/*%', @SQLSegment)
set @fn_offset = 1
set @fn_testText = @SQLSegment
-- it is possible that there are create function statments in comments at the
-- start of the SQL so look for the one that actually creates the function
while @fn_posCommentStart <@fn_posCreateFn and @fn_posCommentStart<>0
begin
set @fn_posCommentEnd = patindex('%*/%', @fn_testText)
set @fn_offset = @fn_offset+@fn_posCommentEnd+1
set @fn_testText = substring(@SQLSegment, @fn_offset, len(@SQLSegment) - @fn_offset)
set @fn_posCreateFn = patindex('%create%function%', @fn_testText collate latin1_general_ci_ai)
set @fn_posCommentStart = patindex('%/*%', @fn_testText)
end
-- now look to see if the owner name is specified, there should be a
-- . before the function name so inspect the text between the word function and the actual
-- function name
set @fn_posFunctionName = charindex('function' collate latin1_general_ci_ai, @SQLSegment collate latin1_general_ci_ai, @fn_posCreateFn-1+@fn_offset)
if @fn_posFunctionName>0
set @fn_posFunctionName=@fn_posFunctionName+8 --number of characters in the word function
set @fn_posObjectName = charindex(@fn_name collate latin1_general_ci_ai, @SQLSegment collate latin1_general_ci_ai, @fn_posFunctionName)
set @fn_testText= substring(@SQLSegment,@fn_posFunctionName,@fn_posObjectName-@fn_posFunctionName)
if charindex('.',@fn_testText,1)=0
begin
-- the owner name is missing, add it
set @SQLSegment = left(@SQLSegment, @fn_posFunctionName) + '['+@fn_owner+'].'+substring(@SQLSegment, @fn_posObjectName,4000)
end
end
UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment
fetch next from @C into @fn_name, @fn_id, @SQLSegment, @fn_owner
end
Close @C
deallocate @C
-- now the permissions on the functions that have been recreated
insert into #SQL (SQL)
select case p.protecttype
when 206 then 'DENY '
else 'GRANT ' end +
case p.action
when 193 then 'SELECT'
when 26 then 'REFERENCES'
end+
' on ['+user_name(o.uid)+'].['+o.name+'] to ['+user_name(p.uid)+']' +
case when p.protecttype = 204 then ' WITH GRANT OPTION' else '' end
from sysprotects p
join sysobjects o
on p.id = o.id
where objectproperty(o.id,'IsTableFunction') =1
-- script out the changing of column level collation
declare @CC_TableName sysname,
@CC_UserName sysname,
@ColName sysname,
@CC_Length nvarchar(100),
@CC_TypeName sysname,
@CC_OtherText nvarchar(4000),
@CC_NullText nvarchar(100),
@CC_id int
set @C = cursor for
select o.id,
u.name,
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
join sysusers u
on u.uid = o.uid
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_id,@CC_UserName,@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_UserName+'].['+@CC_TableName+'] add [____temp] [' + @CC_TypeName + ']'')
-- copy data to temp column
if @@error<>0 set @InError =1
if @@error = 0
exec (''update ['+@CC_UserName+'].['+@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 = @CC_id
and col_name(@CC_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_UserName+'].['+@CC_TableName+'] drop constraint [' + object_name(c.constid) + ']'')
if @@error<>0 set @InError =1
if @@error = 0
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] drop column [' + @ColName + ']'')
if @@error<>0 set @InError =1
if @@error = 0
exec ('' ALTER TABLE ['+@CC_UserName+'].['+@CC_TableName+'] 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 = @CC_id
and (c.status & 5) = 5
and col_name(c.id,c.colid) = @ColName) --default constraint
else
set @SQLSegment = @SQLSegment + '
exec (''Alter table ['+@CC_UserName+'].['+@CC_TableName+'] drop column [' + @ColName + ']'')
-- drop origional column
if @@error<>0 set @InError =1
if @@error = 0
exec (''Alter table ['+@CC_UserName+'].['+@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_UserName+'].['+@CC_TableName+'] set [' + @ColName + '] = [____temp] '')
-- drop temp column
if @@error<>0 set @InError =1
if @@error = 0
exec (''alter table ['+@CC_UserName+'].['+@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_UserName+'].['+@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_UserName+'].['+@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_id,@CC_UserName,@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 ['+u.name+'].[' + o.name + '] WITH NOCHECK ADD CONSTRAINT ['+object_name(cs.constid)+'] CHECK '+sc.text + '
' + case when objectproperty(cs.constid,'CnstIsDisabled') = 1 then 'Alter table ['+u.name+'].[' + o.name + '] NOCHECK CONSTRAINT ['+object_name(cs.constid)+']' else '' end
from sysconstraints cs
join syscomments sc
on sc.id = cs.constid
join sysobjects o
on o.id = cs.id
join sysusers u
on u.uid = o.uid
where objectproperty(cs.constid,'IsCheckCnst') = 1
--script out recreation of calculated columns
insert into #SQL
select 'ALTER TABLE ['+u.name+'].['+o.name+'] ADD ['+c.name+'] AS '+sc.text
from syscolumns c
join syscomments sc
on c.id = sc.id
and c.colid = sc.number
join sysobjects o
on o.id = c.id
join sysusers u
on o.uid = u.uid
where c.iscomputed=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsTable')=1
-- script out the recreation of indexes
-- 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
when (stats & 64) <> 0 then
--statistics
'CREATE STATISTICS ['+index_name+'] on '+objectname+' ('+index_keys+')'
+ case when (stats & 16777216)<>0 then ' WITH ' else @IX_empty end
+ case when (stats & 16777216)<>0 then @IX_des16777216 else @IX_empty end
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
where IsAutoStatistic=0 --do not recreate auto statistics
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 '[' + u.name + '].[' + o.name + ']' as TableName,
object_name(constid) as KeyName,
( select distinct '['+fu.name+'].['+ro.name+']'
from sysforeignkeys fk
join sysobjects ro
on ro.id = fk.rkeyid
join sysusers fu
on fu.uid = ro.uid
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
join sysobjects o
on c.id = o.id
join sysusers u
on u.uid = o.uid
where objectproperty(constid,'IsForeignKey')=1
and constid in (
select fk.constid
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 fc.collationid is not null
or rc.collationid is not null
or 1=1) --paramater allows all constraints to be dropped
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
-- finally set back to multi user access
insert into #SQL values ('alter database [Prism Test] set multi_user')
select * from #SQL order by ID
return
|
|
|
|
|