Thursday, September 23, 2010

Where Are the Constraints?

SELECT
CAST (DATEPART(yyyy, so.crdate) AS VARCHAR) + ' - '
+ CAST (DATEPART(mm, so.crdate) AS VARCHAR) + ' - '
+ CAST( DATEPART(dd, so.crdate) AS VARCHAR) so_crdate
, su.name so_schema
, so.name so_name
, scc.name sc_name
, sct.text sct_constraint
FROM sysobjects so
JOIN syscolumns scc
ON so.id = scc.id
LEFT OUTER JOIN sysusers su
ON so.uid = su.uid
LEFT OUTER JOIN sysconstraints sc
ON so.id = sc.id
AND scc.colid = sc.colid
LEFT OUTER JOIN syscomments sct
ON sc.constid = sct.id
WHERE so.type = 'U'
AND so.crdate > '2010-07-09'
AND so.name NOT LIKE '%bak%'
AND so.name NOT LIKE '%junk%'
AND so.name NOT LIKE '%sav%'
AND su.name = 'dbo'
AND sct.text IS NOT NULL
ORDER BY
so.crdate
, so.name
, scc.name DESC

No comments: