Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Make string columns unicode compliant


Make string columns unicode compliant

Author
Message
Steve Pettifer
Steve Pettifer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 207
Comments posted to this topic are about the item Make string columns unicode compliant



terrance.steadman
terrance.steadman
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 538
Greetings,

Thank you for posting this script to help standardize a database. I think it may come in handy for updating the fields in ours. It would be nice too if the script could also parse through the user created functions and stored procedures within a database and find the non-unicode compliant characters in them too.
Steve Pettifer
Steve Pettifer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 207
I fully agree - I was going to add support for changing data types in stored procedures and UDFs too, but since the database I wrote this for used neither I couldn't justify the time at work!! However, I already have scripts for re-assembling stored procedures so I might well add that support sometime soon when work is a little less chaotic. Mind you, considering that it would involve a lot of string manipulation I'd err on the side of utilising the CLR as it is more efficient at that sort of work. It's something I might have a look at sometime soon, but in the meantime there's quite a few scripts flying about to get hold of stored procedures and functions from a database so it could be an interesting project for someone! :-D



Nadrek
Nadrek
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1238 Visits: 2698
Thank you for the useful and very compact script. While I understand not supporting SQL 2008 features like filtered indexes and data compression, you're also missing support for include columns.

See http://www.sqlservercentral.com/scripts/Indexing/70737/ for references, though your code is much more streamlined.

Also, in your legal disclaimer, wrods->words.
Steve Pettifer
Steve Pettifer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 207
Good spot! I had fully intended to do include columns and forgot. That's something rather useful so I probably will add support for those. This was something of a rush job after a director sprung a demo to a client on us with short notice so there may be one or two errors!



terrance.steadman
terrance.steadman
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 538
Greetings,

May as well let you know: MSUT -> MUST

:-D

Have a good day.
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1238 Visits: 2698
Here's a variant for FK's and CK's that fully mirrors the Trusted/Untrusted, Enabled/Disabled states (it also generates more than one column at once, suitable for greater automation).

If you're going to set things back "the same as they were", then disabled constraints should stay disabled until your constraint maintenance handles them.


IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#CheckSQL]'))
DROP TABLE [dbo].[#CheckSQL]

SELECT
s.[name] AS OriginalSchemaName
,t.[name] AS OriginalTableName
,co.[name] AS OrginalConstraintName
,'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] DROP CONSTRAINT [' + co.[name] + '] ' AS DropCheckSQL
,'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] WITH '
+ CASE WHEN co.[is_disabled] = 0 AND co.[is_not_trusted] = 0 THEN 'CHECK'
ELSE 'NOCHECK'
END
+ ' ADD CONSTRAINT [' + co.[name] + '] CHECK (' + co.[definition] + ') ' AS CreateCheckSQL
,CASE WHEN co.[is_disabled] = 1 THEN 'ALTER TABLE [' + s.[name] + '].[' + t.[name] + '] NOCHECK CONSTRAINT [' + co.[name] + ']'
ELSE NULL
END AS NocheckCheckSQL
INTO #CheckSQL
FROM sys.check_constraints co
INNER JOIN sys.tables t
ON t.[object_id] = co.[parent_object_id]
INNER JOIN sys.schemas s
ON s.[schema_id] = t.[schema_id]
WHERE t.[type] = 'U' --Ensure we only look at user tables.
ORDER BY t.[name], co.[name]

SELECT * FROM #CheckSQL ORDER BY OriginalTableName, OrginalConstraintName


IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ForeignSQL]'))
DROP TABLE [dbo].[#ForeignSQL]

SELECT
s1.[name] AS OriginalSchemaName
,stParent.[name] AS OriginalTableName
,fk.[name] AS OriginalConstraintName
,'ALTER TABLE [' + s1.[name] + '].[' + stParent.[name] + '] DROP CONSTRAINT [' + fk.[name] + '] ' AS DropForeignSQL
,'ALTER TABLE [' + s1.[name] + '].[' + stParent.[name] + '] WITH '
+ CASE WHEN fk.[is_disabled] = 0 AND fk.[is_not_trusted] = 0 THEN 'CHECK'
ELSE 'NOCHECK'
END
+ ' ADD CONSTRAINT [' + fk.[name] + '] FOREIGN KEY (' +
CASE WHEN r.[fkey1] = 0 THEN '' ELSE '[' + c1.[name] + ']' END +
CASE WHEN r.[fkey2] = 0 THEN '' ELSE ', [' + c2.[name] + ']' END +
CASE WHEN r.[fkey3] = 0 THEN '' ELSE ', [' + c3.[name] + ']' END +
CASE WHEN r.[fkey4] = 0 THEN '' ELSE ', [' + c4.[name] + ']' END +
CASE WHEN r.[fkey5] = 0 THEN '' ELSE ', [' + c5.[name] + ']' END +
CASE WHEN r.[fkey6] = 0 THEN '' ELSE ', [' + c6.[name] + ']' END +
CASE WHEN r.[fkey7] = 0 THEN '' ELSE ', [' + c7.[name] + ']' END +
CASE WHEN r.[fkey8] = 0 THEN '' ELSE ', [' + c8.[name] + ']' END +
CASE WHEN r.[fkey9] = 0 THEN '' ELSE ', [' + c9.[name] + ']' END +
CASE WHEN r.[fkey10] = 0 THEN '' ELSE ', [' + c10.[name] + ']' END +
CASE WHEN r.[fkey11] = 0 THEN '' ELSE ', [' + c11.[name] + ']' END +
CASE WHEN r.[fkey12] = 0 THEN '' ELSE ', [' + c12.[name] + ']' END +
CASE WHEN r.[fkey13] = 0 THEN '' ELSE ', [' + c13.[name] + ']' END +
CASE WHEN r.[fkey14] = 0 THEN '' ELSE ', [' + c14.[name] + ']' END +
CASE WHEN r.[fkey15] = 0 THEN '' ELSE ', [' + c15.[name] + ']' END +
CASE WHEN r.[fkey16] = 0 THEN '' ELSE ', [' + c16.[name] + ']' END +
') REFERENCES [' + s2.[name] + '].[' + stReferences.[name] + '](' +
CASE WHEN r.[rkey1] = 0 THEN '' ELSE '[' + c17.[name] + ']' END +
CASE WHEN r.[rkey2] = 0 THEN '' ELSE ', [' + c18.[name] + ']' END +
CASE WHEN r.[rkey3] = 0 THEN '' ELSE ', [' + c19.[name] + ']' END +
CASE WHEN r.[rkey4] = 0 THEN '' ELSE ', [' + c20.[name] + ']' END +
CASE WHEN r.[rkey5] = 0 THEN '' ELSE ', [' + c21.[name] + ']' END +
CASE WHEN r.[rkey6] = 0 THEN '' ELSE ', [' + c22.[name] + ']' END +
CASE WHEN r.[rkey7] = 0 THEN '' ELSE ', [' + c23.[name] + ']' END +
CASE WHEN r.[rkey8] = 0 THEN '' ELSE ', [' + c24.[name] + ']' END +
CASE WHEN r.[rkey9] = 0 THEN '' ELSE ', [' + c25.[name] + ']' END +
CASE WHEN r.[rkey10] = 0 THEN '' ELSE ', [' + c26.[name] + ']' END +
CASE WHEN r.[rkey11] = 0 THEN '' ELSE ', [' + c27.[name] + ']' END +
CASE WHEN r.[rkey12] = 0 THEN '' ELSE ', [' + c28.[name] + ']' END +
CASE WHEN r.[rkey13] = 0 THEN '' ELSE ', [' + c29.[name] + ']' END +
CASE WHEN r.[rkey14] = 0 THEN '' ELSE ', [' + c30.[name] + ']' END +
CASE WHEN r.[rkey15] = 0 THEN '' ELSE ', [' + c31.[name] + ']' END +
CASE WHEN r.[rkey16] = 0 THEN '' ELSE ', [' + c32.[name] + ']' END +
') ' AS CreateForeignSQL
,CASE WHEN fk.[is_disabled] = 1 THEN 'ALTER TABLE [' + s1.[name] + '].[' + stParent.[name] + '] NOCHECK CONSTRAINT [' + fk.[name] + ']'
ELSE NULL
END AS NocheckForeignSQL
INTO #ForeignSQL
FROM sys.foreign_keys fk
INNER JOIN sys.sysreferences r
ON r.[constid] = fk.[object_id]
INNER JOIN sys.tables stParent
ON stParent.[object_id] = r.[fkeyid]
AND stParent.[type] = 'U' --Ensure we only look at user tables.
INNER JOIN sys.tables stReferences
ON stReferences.[object_id] = r.[rkeyid]
INNER JOIN sys.schemas s1
ON s1.[schema_id] = stParent.[schema_id]
INNER JOIN sys.schemas s2
ON s2.[schema_id] = stReferences.[schema_id]
LEFT OUTER JOIN sys.columns c1 ON c1.[object_id] = r.[fkeyid] AND c1.[column_id] = r.[fkey1]
LEFT OUTER JOIN sys.columns c2 ON c2.[object_id] = r.[fkeyid] AND c2.[column_id] = r.[fkey2]
LEFT OUTER JOIN sys.columns c3 ON c3.[object_id] = r.[fkeyid] AND c3.[column_id] = r.[fkey3]
LEFT OUTER JOIN sys.columns c4 ON c4.[object_id] = r.[fkeyid] AND c4.[column_id] = r.[fkey4]
LEFT OUTER JOIN sys.columns c5 ON c5.[object_id] = r.[fkeyid] AND c5.[column_id] = r.[fkey5]
LEFT OUTER JOIN sys.columns c6 ON c6.[object_id] = r.[fkeyid] AND c6.[column_id] = r.[fkey6]
LEFT OUTER JOIN sys.columns c7 ON c7.[object_id] = r.[fkeyid] AND c7.[column_id] = r.[fkey7]
LEFT OUTER JOIN sys.columns c8 ON c8.[object_id] = r.[fkeyid] AND c8.[column_id] = r.[fkey8]
LEFT OUTER JOIN sys.columns c9 ON c9.[object_id] = r.[fkeyid] AND c9.[column_id] = r.[fkey9]
LEFT OUTER JOIN sys.columns c10 ON c10.[object_id] = r.[fkeyid] AND c10.[column_id] = r.[fkey10]
LEFT OUTER JOIN sys.columns c11 ON c11.[object_id] = r.[fkeyid] AND c11.[column_id] = r.[fkey11]
LEFT OUTER JOIN sys.columns c12 ON c12.[object_id] = r.[fkeyid] AND c12.[column_id] = r.[fkey12]
LEFT OUTER JOIN sys.columns c13 ON c13.[object_id] = r.[fkeyid] AND c13.[column_id] = r.[fkey13]
LEFT OUTER JOIN sys.columns c14 ON c14.[object_id] = r.[fkeyid] AND c14.[column_id] = r.[fkey14]
LEFT OUTER JOIN sys.columns c15 ON c15.[object_id] = r.[fkeyid] AND c15.[column_id] = r.[fkey15]
LEFT OUTER JOIN sys.columns c16 ON c16.[object_id] = r.[fkeyid] AND c16.[column_id] = r.[fkey16]
LEFT OUTER JOIN sys.columns c17 ON c17.[object_id] = r.[rkeyid] AND c17.[column_id] = r.[rkey1]
LEFT OUTER JOIN sys.columns c18 ON c18.[object_id] = r.[rkeyid] AND c18.[column_id] = r.[rkey2]
LEFT OUTER JOIN sys.columns c19 ON c19.[object_id] = r.[rkeyid] AND c19.[column_id] = r.[rkey3]
LEFT OUTER JOIN sys.columns c20 ON c20.[object_id] = r.[rkeyid] AND c20.[column_id] = r.[rkey4]
LEFT OUTER JOIN sys.columns c21 ON c21.[object_id] = r.[rkeyid] AND c21.[column_id] = r.[rkey5]
LEFT OUTER JOIN sys.columns c22 ON c22.[object_id] = r.[rkeyid] AND c22.[column_id] = r.[rkey6]
LEFT OUTER JOIN sys.columns c23 ON c23.[object_id] = r.[rkeyid] AND c23.[column_id] = r.[rkey7]
LEFT OUTER JOIN sys.columns c24 ON c24.[object_id] = r.[rkeyid] AND c24.[column_id] = r.[rkey8]
LEFT OUTER JOIN sys.columns c25 ON c25.[object_id] = r.[rkeyid] AND c25.[column_id] = r.[rkey9]
LEFT OUTER JOIN sys.columns c26 ON c26.[object_id] = r.[rkeyid] AND c26.[column_id] = r.[rkey10]
LEFT OUTER JOIN sys.columns c27 ON c27.[object_id] = r.[rkeyid] AND c27.[column_id] = r.[rkey11]
LEFT OUTER JOIN sys.columns c28 ON c28.[object_id] = r.[rkeyid] AND c28.[column_id] = r.[rkey12]
LEFT OUTER JOIN sys.columns c29 ON c29.[object_id] = r.[rkeyid] AND c29.[column_id] = r.[rkey13]
LEFT OUTER JOIN sys.columns c30 ON c30.[object_id] = r.[rkeyid] AND c30.[column_id] = r.[rkey14]
LEFT OUTER JOIN sys.columns c31 ON c31.[object_id] = r.[rkeyid] AND c31.[column_id] = r.[rkey15]
LEFT OUTER JOIN sys.columns c32 ON c32.[object_id] = r.[rkeyid] AND c32.[column_id] = r.[rkey16]

SELECT * FROM #ForeignSQL ORDER BY OriginalTableName, OriginalConstraintName


Steve Pettifer
Steve Pettifer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 207
It already does pay attention to trusted/untrusted and enabled/disabled status - and generates all columns without the need for loops/cursor or temp tables.

Cheers



Nadrek
Nadrek
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1238 Visits: 2698
It doesn't actually disable constraints that were previously disabled; run a quick test and see for yourself.
Steve Pettifer
Steve Pettifer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 207
I hate to use the old developers refrain but...it worked on my db. There were several constraints which had been disabled and they were again afterwards. I will re check this but I'm pretty certain it does this already.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search