[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

pick only DEFAULT constraints from sysconstraints

Posted on 2012-08-23
7
Medium Priority
?
812 Views
Last Modified: 2012-08-28
how can you filter only the DF constraints from the below:?
 SELECT object_name(constid),object_name(id),COL_NAME(id,colid) FROM sysconstraints
0
Comment
Question by:25112
  • 5
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38327795
Is this just for SQL Server 2000?  I ask because you have posted to SQL Server 2008, yet you are using the deprecated SQL Server 2000 System table.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38327803
In any case if you still need to use that old table, in order to get the DEFAULT constraints all you have to do is add the following WHERE clause:
WHERE status & 5 = 5
0
 
LVL 5

Author Comment

by:25112
ID: 38329200
thanks- that helped.

i chose sysconstraints because it was neutral to all environments...

for 2008, i came up with
select name,OBJECT_NAME(parent_object_id) ,*  from sys.all_objects where type = 'd'

but i am not able to pull the column name.. can you advise?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38329364
but i am not able to pull the column name.. can you advise?
For which version SQL Server 2000 or 2008? The answer should be different in each case.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38333267
Assuming you are still using SQL Server 2000, then this is how you would have to do it:
SELECT  o.name, c.name, oc.name
FROM    sysobjects o
	INNER JOIN syscolumns c ON o.id = c.id
	INNER JOIN sysconstraints ct ON o.id = ct.id AND c.colid = ct.colid
	INNER JOIN sysobjects oc ON ct.constid = oc.id
WHERE	o.xtype = 'U'
	AND ct.status & 5  = 5

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 38336214
acperkins- thanks for that. sorry i should have kept the intended zone more clear..

can you kindly give me the syntax for 2008 to get column name? appreciate that.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38336259
I believe we covered that in your other thread. In any case, here it goes again:
SELECT  t.name,
        dc.name
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
                                                 AND c.column_id = dc.parent_column_id

Open in new window

0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question