March 12, 2007 at 3:16 pm
We have a sql 2000 database with tons (as in TONS) of User-Defined types. Typical schema browsing experience (field name, field type):
...
[VhType] [RevID] NULL,
[VhUsage] [VhUsage] NULL,
....
[VHLoad] [VHLoad] NOT NULL,
...
And the data types (i.e. RevID, VHUsage, VHLoad) show up as such when the database is reverse engineered. I'd like to replace the UDF by the native types. How would you do this?
TIA,
Bill
March 12, 2007 at 4:53 pm
I think Only option will be ALTER TABLE command for each table..
MohammedU
Microsoft SQL Server MVP
March 12, 2007 at 9:32 pm
I think Mohammed is right. However have you tried simply deleting the UDTs?
If that doesn't work maybe you would be able to just unbind them which would be much faster to do than alter all the tables.
March 12, 2007 at 10:41 pm
Interesting point. I'll try it out.
Bill
March 12, 2007 at 10:49 pm
I tried deleting the UDT but, as expected, Sql said I couldn't do that because the UDT was being used.
Any suggesitons how to unbind the UDT?
TIA,
Bill
March 12, 2007 at 11:01 pm
Not fully tested but this worked in a simple test case.
Used Sql 2005 SSIS (maybe even Sql 2000 DTS) to copy the source table (with UDT) over to a non-existant destinaiton table in a new database. In my test it correctly replaced the UDT (source) with a bigint destination field. I'll test it out more thoroughly but it seems to have a chance of working.
Bill
March 13, 2007 at 6:32 am
Ya but you still have to recreate the whole db somehow without losing any data. I'm sure there's a way to automate this task without causing too much grief on the server.
March 13, 2007 at 12:14 pm
This script generates ALTER TABLE commands for all columns with user-defined types. I hope you don't have any evil side-effects from a wholesale type change, but I can't think of one at the moment. The normal restrictions on ALTER COLUMN (no default value, not in a primary key, etc) don't seem to apply to changing a UDT to the underlying type, at least in the simple tests I ran.
Anyway, you asked for it:
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
'ALTER TABLE <db>.<schema>.<table> ALTER COLUMN <column> <type><size><collation> <null>',
'<db>', QUOTENAME(TABLE_CATALOG)),
'<schema>', QUOTENAME(TABLE_SCHEMA)),
'<table>', QUOTENAME(TABLE_NAME)),
'<column>', QUOTENAME(COLUMN_NAME)),
'<type>', DATA_TYPE),
'<size>', CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN REPLACE('(s)','s',CHARACTER_MAXIMUM_LENGTH)
WHEN DATA_TYPE IN ('numeric','decimal') THEN REPLACE(replace('(p,s)','p',NUMERIC_PRECISION),'s',NUMERIC_SCALE) ELSE '' END),
'<collation>', CASE WHEN COLLATION_NAME IS NOT NULL THEN ' COLLATE ' + COLLATION_NAME ELSE '' END),
'<null>', CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END)
FROM information_schema.columns
WHERE data_type <> domain_name
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply