Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vision.v1
Yak Posting Veteran
72 Posts |
 Posted - 2010-10-06 : 09:20:12
|
| Hi, How can i identify a particular column in table say 'colum1' contains unique key constraint or not, need to check dynamically in a stored procedure...Thanks |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-06 : 09:24:58
|
| why would you need to do this?the sys.key_constraints system view can give you this information.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
vision.v1
Yak Posting Veteran
72 Posts |
Posted - 2010-10-06 : 09:35:54
|
| Hi, i want to get all the tables with prefix 'cust_' from my database, and check the field custno in the table whether it hasUNIQUE key or not if UNIQUE key is not defined for 'custno' field i need to add UNIQUE key dynamically...Thanks |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-06 : 09:59:44
|
IF there's only one UNIQUE constraint per table this would find them.SELECT tab.[name] AS [Table] , skc.[name]FROM sys.objects AS tab LEFT JOIN sys.key_Constraints AS skc ON skc.[parent_object_id] = tab.[object_id] AND skc.[type] = 'UQ'WHERE tab.[type_desc] = 'USER_TABLE' AND tab.[name] LIKE 'cust_%'ORDER BY tab.[name] It's a little harder to associate the unique key to an actual column (I'm sure there is an easier way but I'm too busy to look for it)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
How to check Unqiue key dynamically in a table