Prev: Where clause not working
Next: First Function SQL Server 2000
From: mavrick_101 on 23 Mar 2010 15:43
Hi,

How can I get all the constraints specified on all the tables in a given
database?

Thanks for your help.

From: Michael MacGregor on 23 Mar 2010 16:48
Which version of SQL Server are you using?

Michael MacGregor


From: mavrick_101 on 23 Mar 2010 16:41
Both 2005 and 2008

Thnx



"Michael MacGregor" wrote:

> Which version of SQL Server are you using?
>
> Michael MacGregor
>
>
> .
>
From: Michael MacGregor on 23 Mar 2010 18:13
For 2005, in Management Studio, right click on the database concerned,
select Tasks then Generate Scripts. The Generate SQL Server Scripts Wizard
will start, click Next. You will be shown a list of databases, not sure why
it does this when you've started it by right clicking on a database but
nevertheless select the database you are interested in and click Next. You
will then see a list of options, at the bottom are a list of Table/View
Options, select the ones you are interested in and then click Next. Select
the type of object you are intersted in, Tables, click Next. Select the
specific tables you are interested in, if all then click Select All, then
click Next. Then selct the Output Option as appropriate, click Next. A
Summary of what you selected will be displayed, if you made a mistake now is
the time to check it and go Back to change it, otherwise click Finish.

Unfortunately it will also generate the CREATE TABLE DDL as it doesn't seem
to be flexible enough to not do that, which has been a bone of contention
for me with SQL, so you will need to edit the script afterwards if you don't
want the CREATE TABLE. Not sure what the options are in 2008, might be
better.

Alternatively you can find a nice third party tool that could do it, someone
else might be able to point you in the right direction for that. Or if you
are feeling really adventurous you can use the system views and/or the
Information Schema Views to reverse engineer the constraints. I do have some
code for that but unfortunately it is on my main computer which is currently
out of service due to a hardware fault.

Michael MacGregor


From: Plamen Ratchev on 23 Mar 2010 17:23
Take a look at the following catalog views:

sys.check_constraints
sys.default_constraints
sys.key_constraints
sys.foreign_keys

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Where clause not working
Next: First Function SQL Server 2000