List Constraints For A Column

[Ambsgirl]

How do I list all the constriants that exist on one column? sp_helpconstraint will list all constraints for the table as does select * from sysobjects where objectproperty(id,N'IsConstraint')=1 and
parent_obj=coalesce(object_id('di_menus'),'-1').

I'm trying to delete a column programmatically and the constraints on that column could vary per db so it needs to be dynamic.

Thanks!

 

[ESquared]

use master
sp_helptext 'sp_helpconstraint'

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Ambsgirl]

Actually, that doesn't tell me quite what I want to know. It returns all constraints for all columns in the table which would be great if it had a separate column saying the column the constraint is on, but it doesn't. It gives a description with the column name but that varies depending on the constraint.

I've managed to come up with something that appears to be working: select sysobjects.name as 'sysobjects.name' From sysobjects, syscolumns Where sysobjects.parent_obj = syscolumns.id
and sysobjects.id = syscolumns.cdefault and objectproperty(sysobjects.id,N'IsConstraint')=1 and parent_obj=coalesce(object_id('<table name>'),'-1') and syscolumns.name='<column name>'. It doesn't include primary keys but I'm not sure there is one statement that will.

 

[ESquared]

The reason I posted as I did was two-fold:

1 - to tell you where to find the information you needed (which you found).
2 - to help you learn something that can help you find the information you need yourself next time, without having to ask (and only you will know if this succeeded).

So how about you make your own version of that sp yourself that accepts a column name? In any case, you've already gotten a good start on the answer you need.

So keep trying. If you can't make it work, ask for help. If I just feed you the answer, what good is that to you?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Ambsgirl]

Thanks for the advice, Esquared. While I appreciate your interest in my ability to solve my own problems, I was under the impression that these forums exist for the purpose of allowing people to help each other. sp_helpconstraint was something I already knew about as I stated in my original thread so you didn't actually tell me where to find the information I needed or help me learn anything new. But keep trying. Thanks.

 

[ESquared]

I am helping you, if not precisely in the mode you would find the least amount of work on your part. Even the least amount of work on my part.

Each person who answers has his own style. And mood. So sometimes we just give gold-plated working answers. But sometimes we try to give a little kick to get the person more used to solving the problem with his own resources. Which is actually of greater benefit to the asker in the long run? You know the adage about "give a man a fish / teach a man to fish." It's overused, but true.

So the contents of sp_helpconstraint didn't teach you what you need to know about the system tables to query them? Did you actually look at the contents?

I'll try it from another angle. You may not know that there are some views in the database under the owner name "information_schema." These views will help you pull together most of what you need to know. You still have to hit the system tables to get everything completely, but it can get you started.

A google search for "scripting sql server constraints information_schema" yields Script out PKs/UNIQUE constraints and referencing FKs.

If that still doesn't do it, post back and we'll get you going. Maybe I'll even give you a working query I have that took me hours of work to put all together. But you are being tested. The attitude of a questioner definitely has something to do with an answerer's willingness to give away hard work for free in an instant.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[ESquared]

Btw if you're using sql server 2005 there are some differences.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Ambsgirl]

I wasn't aware that the usefulness of the answer directly coincides with the amount of hours I had already invested in the problem. If I were, I would have told you that I rolled up my sleeves hours ago. I'm certainly not looking to encroach on anyone's hours of hard work who is unwilling. I assumed that people who answer these posts are willing to contribute their knowledge. It never occured to me that I would have to convince a total stranger that I am worthy, have good morals and am not lazy.

At any rate, I appreciate the time you've taken but have found my last statement does the job.

 

[ESquared]

Any time you ask complete strangers for anything, if you want squate it helps to be polite.

Politeness in each context is determined by the culture of the place, and the history, and the actual people there. Maybe I misread things. Maybe you did. But one thing that's for sure is that there are good ways to ask questions and not so good ways.

If you consult How To Ask Questions The Smart Way it could help you understand why I came from where I did and that in fact I was operating in your best interest with a real desire to actually help you. And that your reply to someone who was only trying to help you was actually pretty darn rude.

Spoonfeeding, in my opinion, doesn't fit that bill. It's a compliment that I didn't simply do that: it means I thought you were ready for something better.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[philhege]

Give a man a fish, and he'll eat for a day.

Teach a man how to fish, and he'll sit out on the lake all day and drink beer.

I guess you pointed ambsgirl in the general direction of the lake.

We all get donor fatigue every now and then. Some requests are very obviously student questions and we avoid those like the plague (although I did answer a "recipe database" question the other day). I hope no one is turned away by the tone of some replies. We are all genuinely willing to help, just not to write someone's entire solution for them.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[ESquared]

Ambsgirl,

I apologize for my earlier grumpiness today.

Here. Take it. Built to assist dropping and recreating constraints. Mine it for all it's worth. Pure gold.

I make no promises as to completeness. There are aspects still missing, like ASC/DESC on keys, "NOT FOR REPLICATION" clauses, rules and defaults bound with sp_bindrule and sp_bindefault, and who knows all what else. But then, a quick review of the text of sp_helpconstraint could point out those defects to an alert person.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KeyColumnUsage]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[KeyColumnUsage]
GO
CREATE VIEW KeyColumnUsage
AS
SELECT K.Constraint_Name,
   Column_Names = Substring(
      Max(CASE K.Ordinal_Position WHEN 1 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 2 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 3 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 4 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 5 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 6 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 7 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 8 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 9 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 10 THEN ', ' + QuoteName(K.Column_Name) ELSE '' END)
      , 3, 3000),
   Column_Names2 = Substring(
      Max(CASE K.Ordinal_Position WHEN 1 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 2 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 3 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 4 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 5 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 6 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 7 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 8 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 9 THEN '_' + K.Column_Name ELSE '' END)
      + Max(CASE K.Ordinal_Position WHEN 10 THEN '_' + K.Column_Name ELSE '' END)
      , 2, 2000),
   ColumnCount = Count(*)
FROM
   Information_Schema.Key_Column_Usage K
GROUP BY
   K.Constraint_Name

CREATE VIEW ColumnDefaults
AS
SELECT
   CONSTRAINT_NAME = O.[name],
   TABLE_CATALOG = DB_Name(),
   TABLE_SCHEMA = User_Name(O.uid),
   TABLE_NAME = T.[name],
   COLUMN_NAME = L.[name],
   DEFAULT_CLAUSE = C.[text]
FROM sysobjects O
INNER JOIN syscomments C on O.[id] = C.[id]
INNER JOIN sysobjects T on O.parent_obj = T.[id ]
INNER JOIN sysconstraints N on O.[id] = N.constid
INNER JOIN syscolumns L on T.[id] = L.[id] AND N.colid = L.colid
WHERE
   O.xtype = 'D'
   AND T.[name] <> 'dtproperties'

SELECT
   [Drop] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' DROP CONSTRAINT ' + QuoteName(C.Constraint_Name)),
   [Recreate] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' ADD CONSTRAINT ' + QuoteName(C.Constraint_Name)
   + ' ' + C.Constraint_Type)
   + CASE WHEN C.Constraint_Type = 'CHECK' THEN ' ' + CC.Check_Clause ELSE '' END
   + CASE C.Constraint_Type WHEN 'FOREIGN KEY' THEN ' (' + K.Column_Names + ') REFERENCES ' + QuoteName(FK.Table_Schema) + '.' + QuoteName(FK.Table_Name) + ' (' + FKK.Column_Names + ')' ELSE '' END
   + CASE C.Constraint_Type
      WHEN 'PRIMARY KEY' THEN ' ' + CASE IndexProperty(Object_Id(QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name)), C.Constraint_Name, 'IsClustered') WHEN 1 THEN '' ELSE 'NON' END + 'CLUSTERED (' + K.Column_Names + ')'
         + CASE WHEN I.OrigFillFactor <> 0 THEN ' WITH FILLFACTOR = ' + Convert(varchar(11), I.OrigFillFactor) ELSE '' END
         + CASE WHEN G.GroupName <> 'PRIMARY' THEN ' ON ' + QuoteName(G.GroupName) ELSE '' END
      ELSE '' END
   + CASE WHEN R.Update_Rule <> 'NO ACTION' THEN ' ON UPDATE ' + R.Update_Rule ELSE '' END
   + CASE WHEN R.Delete_Rule <> 'NO ACTION' THEN ' ON DELETE ' + R.Delete_Rule ELSE '' END
FROM
   Information_Schema.Table_Constraints C
   LEFT JOIN dbo.KeyColumnUsage K ON K.Constraint_Name = C.Constraint_Name
   LEFT JOIN Information_Schema.Referential_Constraints R ON K.Constraint_Name = R.Constraint_Name
   LEFT JOIN Information_Schema.Table_Constraints FK ON R.Unique_Constraint_Name = FK.Constraint_Name
   LEFT JOIN dbo.KeyColumnUsage FKK ON R.Unique_Constraint_Name = FKK.Constraint_Name
   LEFT JOIN Information_Schema.Check_Constraints CC ON C.Constraint_Name = CC.Constraint_Name
   LEFT JOIN SysIndexes I ON C.Constraint_Name = I.[Name]
   LEFT JOIN SysFileGroups G ON I.GroupID = G.GroupID
WHERE
   C.Table_Name <> 'dtproperties'

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[N2Code]

Hey AmbsGirl, I realize that sometimes a person is on a time constraint and is looking for an answer to quickly solve a problem, not for a long debate on the morals and ethics and writing standards for asking and answering questions. I've been down your road as well, and this select statement gave me all the constraints on a particular table, while listing the column name in the results. Unfortunately, the only constraint it did not list is the primary key, but fortunately, that one is easy to get.

This will get you all the other constraints:

select sysobjects.name, syscolumns.name
from sysobjects, syscolumns
where sysobjects.parent_obj = syscolumns.id
and sysobjects.id = syscolumns.cdefault
and objectproperty(sysobjects.id,N'IsConstraint')=1 and
parent_obj=coalesce(object_id('<table name>'),'-1')

And, of course, to grab any primary key, you can run this:
sp_pkeys '<table name>'

From the results, I'm sure you can figure out how to get what you want for a specific column.

Hope this helps you and is faster than the others were.

 

[Ambsgirl]

Esquared, that is definitely more than I bargained for. I see why it took you hours to put that together. I'll play around with it and see what happens. Thanks for your help and your time.

 

[Ambsgirl]

N2Code, I've also discovered that and now that I know you are using it as well, I feel more confident in trusting it. Thanks a million for your help and your quick response.

 

[ESquared]

Multiple-column primary and foreign keys... are they pulled out correctly by the queries you're using?

I honestly did what I thought would be the most helpful. And I was honestly surprised at the response.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[Ambsgirl]

Unfortunately, that query does not show primary or foreign keys which is why you have to use sp_pkeys and sp_fkeys for that info. But it does seem to give you all other constraint info.

I do not doubt that you did what you thought was most helpful and I do appreciate your intent if not your delivery.

 

[ESquared]

N2Code,

I was still 3 minutes faster than you. Sorry, couldn't resist.

Ambsgirl,

A mistake, a fix, some minor changes, too:

+ CASE C.Constraint_Type WHEN 'UNIQUE' THEN ' (' + K.Column_Names + ')' ELSE '' END

SELECT
   [Drop] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' DROP CONSTRAINT ' + QuoteName(C.Constraint_Name)),
   [Create] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' ADD CONSTRAINT ' + QuoteName(C.Constraint_Name)
   + ' ' + C.Constraint_Type[COLOR=white red] [/color]
   + CASE C.Constraint_Type WHEN 'CHECK' THEN ' ' + CC.Check_Clause ELSE '' END
   [b]+ CASE C.Constraint_Type WHEN 'UNIQUE' THEN ' (' + K.Column_Names + ')' ELSE '' END[/b]
   + CASE C.Constraint_Type WHEN 'FOREIGN KEY' THEN ' (' + K.Column_Names + ') REFERENCES ' + QuoteName(FK.Table_Schema) + '.' + QuoteName(FK.Table_Name) + ' (' + FKK.Column_Names + ')' ELSE '' END
   + CASE C.Constraint_Type
      WHEN 'PRIMARY KEY' THEN ' ' + CASE IndexProperty(Object_Id(QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name)), C.Constraint_Name, 'IsClustered') WHEN 1 THEN '' ELSE 'NON' END + 'CLUSTERED (' + K.Column_Names + ')'
         + CASE WHEN I.OrigFillFactor <> 0 THEN ' WITH FILLFACTOR = ' + Convert(varchar(11), I.OrigFillFactor) ELSE '' END
         + CASE WHEN G.GroupName <> 'PRIMARY' THEN ' ON ' + QuoteName(G.GroupName) ELSE '' END
      ELSE '' END
   + CASE WHEN R.Update_Rule <> 'NO ACTION' THEN ' ON UPDATE ' + R.Update_Rule ELSE '' END
   + CASE WHEN R.Delete_Rule <> 'NO ACTION' THEN ' ON DELETE ' + R.Delete_Rule ELSE '' END[red][b])[/b][/red]
FROM
   Information_Schema.Table_Constraints C
   LEFT JOIN dbo.KeyColumnUsage K ON K.Constraint_Name = C.Constraint_Name
   LEFT JOIN Information_Schema.Referential_Constraints R ON K.Constraint_Name = R.Constraint_Name
   LEFT JOIN Information_Schema.Table_Constraints FK ON R.Unique_Constraint_Name = FK.Constraint_Name
   LEFT JOIN dbo.KeyColumnUsage FKK ON R.Unique_Constraint_Name = FKK.Constraint_Name
   LEFT JOIN Information_Schema.Check_Constraints CC ON C.Constraint_Name = CC.Constraint_Name
   LEFT JOIN SysIndexes I ON C.Constraint_Name = I.[Name]
   LEFT JOIN SysFileGroups G ON I.GroupID = G.GroupID
WHERE
   C.Table_Name <> 'dtproperties'

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[ESquared]

It seems there was a missing piece, too. Here it is.

SELECT
   [Drop] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' DROP CONSTRAINT ' + QuoteName(C.Constraint_Name)),
   [Create] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' ADD CONSTRAINT ' + QuoteName(C.Constraint_Name) + ' DEFAULT ' + DEFAULT_CLAUSE + ' FOR ' + QuoteName(C.Column_Name))
FROM
   ColumnDefaults C

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[ESquared]

more tweaks

SELECT
   [Drop] = Convert(varchar(1000), 'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' DROP CONSTRAINT ' + QuoteName(C.Constraint_Name)),
   [Create] =
      Convert(varchar(1000),
         'ALTER TABLE ' + QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name) + ' ADD CONSTRAINT ' + QuoteName(C.Constraint_Name)
         + ' ' + C.Constraint_Type
          + CASE C.Constraint_Type
         WHEN 'CHECK' THEN ' ' + CC.Check_Clause
         WHEN 'DEFAULT' THEN ' ' + DEFAULT_CLAUSE + ' FOR ' + QuoteName(C.Column_Name)
         WHEN 'FOREIGN KEY' THEN ' (' + K.Column_Names + ') REFERENCES ' + QuoteName(FK.Table_Schema) + '.' + QuoteName(FK.Table_Name) + ' (' + FKK.Column_Names + ')'
            + CASE WHEN R.Update_Rule <> 'NO ACTION' THEN ' ON UPDATE ' + R.Update_Rule ELSE '' END
            + CASE WHEN R.Delete_Rule <> 'NO ACTION' THEN ' ON DELETE ' + R.Delete_Rule ELSE '' END
         WHEN 'PRIMARY KEY' THEN ' '
            + CASE IndexProperty(Object_Id(QuoteName(C.Table_Schema) + '.' + QuoteName(C.Table_Name)), C.Constraint_Name, 'IsClustered') WHEN 1 THEN '' ELSE 'NON' END + 'CLUSTERED (' + K.Column_Names + ')'
            + CASE WHEN I.OrigFillFactor <> 0 THEN ' WITH FILLFACTOR = ' + Convert(varchar(11), I.OrigFillFactor) ELSE '' END
            + CASE WHEN G.GroupName <> 'PRIMARY' THEN ' ON ' + QuoteName(G.GroupName) ELSE '' END
         WHEN 'UNIQUE' THEN ' (' + K.Column_Names + ')'
         ELSE ''
         END
      )
FROM
   (
      SELECT Table_Schema, Table_Name, Constraint_Name, Constraint_Type = 'DEFAULT', Column_Name, Default_Clause FROM ColumnDefaults
      UNION ALL SELECT Table_Schema, Table_Name, Constraint_Name, Constraint_Type, NULL, NULL FROM Information_Schema.Table_Constraints
   ) C
   LEFT JOIN dbo.KeyColumnUsage K ON K.Constraint_Name = C.Constraint_Name
   LEFT JOIN Information_Schema.Referential_Constraints R ON K.Constraint_Name = R.Constraint_Name
   LEFT JOIN Information_Schema.Table_Constraints FK ON R.Unique_Constraint_Name = FK.Constraint_Name
   LEFT JOIN dbo.KeyColumnUsage FKK ON R.Unique_Constraint_Name = FKK.Constraint_Name
   LEFT JOIN Information_Schema.Check_Constraints CC ON C.Constraint_Name = CC.Constraint_Name
   LEFT JOIN SysIndexes I ON C.Constraint_Name = I.[Name]
   LEFT JOIN SysFileGroups G ON I.GroupID = G.GroupID
WHERE
   C.Table_Name <> 'dtproperties'

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[N2Code]

ESquared,

True, you did post your answer 3 minutes before mine. But I can't help but wonder if Ambsgirl was able to implement your solution or mine faster and easier. Although, I must say yours looks very thorough! I guess it all depends on what Ambsgirl is doing with it. Thanks for your input. Since I sometimes have to deal with column constraints myself, I will copy this code and guard it like gold.