| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-02-10 : 18:16:52
|
| Hi experts,Well, the Subject line says it all.I'm trying to find a way to script ALL user data types in a 2005 database. Seems that you can't do it using Management Studio but maybe someone has a script. There are hundreds of user data types and it will be very painful to create them manually.Thanks, John |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-10 : 19:25:14
|
Do you mean user defined datatypes?You can get the datatypes of all existing table columns using SELECT DATA_TYPE FROM INFORMATION_SCHEMA.Columns GROUP BY DATA_TYPE
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-11 : 05:03:41
|
| Hundreds of user data types? I though user data types were used to standardize on a few only...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-02-11 : 05:08:05
|
there are probably hundreds of user and each create their own data type ? 
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-02-11 : 06:31:23
|
| Just don't know what is this fantasy with user defined data types.Till now I have created not more than 2 or 3 user defined data types in my whole career.PBUH |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-02-11 : 06:34:02
|
| I mainly use them for describing table variables that I want to be able to pass into stored procs.And yeah -- hundreds of different user data types????????whoa.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-02-11 : 14:15:37
|
| Sachin - Oh I agree! I almost never create a user defined data type. But this is software that we (unfortunately) purchased.Jezemine - Thanks for the script, i will try it.yes these are USER data types.Thanks to all of you. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-11 : 14:25:38
|
I love user-defined data type. So there!  Haven't used hundreds of them in a database though, maybe 20 at most.You can also script out all user-defined type definitions using the Generate Scripts feature in Management Studio. |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-02-11 : 17:07:03
|
| Yes robvolk I had found it by stumbling around. Right-click database name --> Tasks leads you to the form where you can select specific object types.It is still running - I'm letting the script generate a .sql file.Nice feature. Maybe in SQL Server xxxx they will do away with user data types. Nah.John |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-11 : 17:20:40
|
quote:
Maybe in SQL Server xxxx they will do away with user data types
Only if they replace them with proper domains.BTW, they added CLR data types and use them to implement of number of new data types in SQL 2008. Kinda pointless to add such a feature and then drop it 2 versions later (although they've done that a few times before)  |
|
|
|
|
How to Script All Data Types?