pull/capture data from sp_HelpConstraint

Question

Within SQL Server 2005 T-SQL, I would really like to pull these columns:

constraint_type, constraint_name, and constraint_keys

from the output of sp_HelpConstraint. However it returns 3 result sets (2 if you pass in 'nomsg'), so you can't do this to capture it:

CREATE TABLE #Constraints
(
...
)

INSERT INTO #Constraints
        (...        )
    EXECUTE sp_HelpConstraint 'TableName', 'nomsg'

The only ways I can think of doing this are not good ones:

• just copy the code I need from sp_HelpConstraint
• "fix" sp_HelpConstraint so 'nomsg' removes the last result set too

any ideas?

Answer 1

Hopefully, you're on SQL 2005+ sys.default_constraints etc to allow standard SELECT

Otherwise, you can use Information Schema Views on SQL 2000 +