4

I have the following SQL:

 ALTER TABLE dbo.PS_userVariables DROP CONSTRAINT PK_PS_userVariables;
 ALTER TABLE dbo.PS_userVariables ADD PRIMARY KEY (varnumber, subjectID, userID, datasetID, listid, userVarTitle);

Since I have multiple environments, that PK_PS_userVariables constraint name is different on my different databases. How do I write a script that gets that name then adds it into my script?

cdub
  • 22,735
  • 52
  • 164
  • 287
  • Do you want to drop all the constraints, or just one in particular? – OMG Ponies Jul 13 '11 at 18:35
  • i want to drop all the primary keys on that table, and then add in my extra primary keys – cdub Jul 13 '11 at 18:40
  • http://stackoverflow.com/questions/6115451/how-to-generate-all-constraints-scripts, or: http://stackoverflow.com/search?q=[sql-server]+drop+constraints – OMG Ponies Jul 13 '11 at 18:41
  • 1
    There can only be one primary key on a table. That's why it's called primary. :-) – Aaron Bertrand Jul 13 '11 at 18:48
  • @aaron whay do you mean there can only be one primary key? Are you talking about the columns or the fact that the primary key means the data is normalized? – cdub Jul 13 '11 at 19:08
  • I'm saying that you can only add a single PRIMARY KEY constraint to a table (regardless of how many columns), so "all the primary keys on that table" is the same as "THE primary key on that table." – Aaron Bertrand Jul 13 '11 at 19:17

3 Answers3

13

While the typical best practice is to always explicitly name your constraints, you can get them dynamically from the catalog views:

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

SELECT @table = N'dbo.PS_userVariables';

SELECT @sql = 'ALTER TABLE ' + @table 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

EXEC sp_executeSQL @sql;

ALTER TABLE dbo.PS_userVariables ADD CONSTRAINT ...
Bertrand Le Roy
  • 17,681
  • 2
  • 28
  • 33
Aaron Bertrand
  • 261,961
  • 36
  • 448
  • 471
3
SELECT 
   A.TABLE_NAME, 
   A.CONSTRAINT_NAME, 
   B.COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
      CONSTRAINT_TYPE = 'PRIMARY KEY' 
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 
   A.TABLE_NAME

Ref: Pinal Dave @ http://blog.sqlauthority.com/2008/09/06/sql-server-find-primary-key-using-sql-server-management-studio/

Chains
  • 12,111
  • 8
  • 43
  • 62
  • 1
    Unless you need to strictly adhere and cater to cross-platform, I would caution against using the INFORMATION_SCHEMA views. In this case it might be okay but in a lot of cases, since the I_S views are not being maintained, they are missing key information that you need to go to the catalog views for anyway (e.g. indexes - INCLUDE columns, filters, etc. are nowhere to be found in INFORMATION_SCHEMA). – Aaron Bertrand Jul 13 '11 at 18:49
  • @Aaron: I agree, but I'm all for easier is better when I can get away with it, and Info schema should do fine in this particular case, and will scale better anyway. – Chains Jul 13 '11 at 19:11
  • I'm more for consistent code than easier code. People learning to use INFORMATION_SCHEMA then struggle with the information it doesn't include in those outlier cases. Anyway, how will that "scale" better? Do you mean you can copy that exact code and run it on some other RDBMS? That is not what most people refer to as "scale" but rather "portability"... – Aaron Bertrand Jul 13 '11 at 19:18
  • Not trying to argue semantics -- easy is not contrary to consistent. Consistency is good, but that's an argument FOR info schema, not against it. sys views will be re-defined over time, with new versions of the server, and that's what I mean by scalable -- info schema dependencies you write today should still be viable with the next version. Sys view dependencies, on the other hand, will need to be double-checked. Regardless, your way works, my way works, and I think they're BOTH good to know, but one isn't necessarily better than the other -- just depends on the job at hand. – Chains Jul 13 '11 at 19:29
  • Not sure how consistency is an argument FOR INFORMATION_SCHEMA in the case where you are only dealing with SQL Server (most people don't jump to different RDBMS platforms throughout the day, and most companies don't migrate to different platforms overnight). What I'm talking about when I'm saying consistency is using INFORMATION_SCHEMA for primary keys but sys.indexes etc. for indexes. – Aaron Bertrand Jul 13 '11 at 19:32
  • I guess I'd just use whatever I needed. I use both sys views and info schema (and other meta data views, for that matter, not just those two). Are you saying you'd NEVER use info schema, and just ALWAYS use sys views, no matter what? Not sure what the value of consistency is there, but OK -- I'm sure that will work for you. Why do you think MS came-up with info-schema then? Maybe because it anticipates and provides for the most common needs, and toward that end, they wanted to make things...wait for it...*easier*? :-) Just playing with you. You already won the question anyway. :-P – Chains Jul 13 '11 at 19:39
  • Also not sure why you think INFORMATION_SCHEMA dependencies are more reliable than sys views. Anyway, if you want to keep using INFORMATION_SCHEMA when it makes sense for you, by all means, go ahead. I just want to make sure future readers understand that there may be some reasons they don't want to do that (again, because these views are not being developed, they are never going to contain information about new features, object types, or properties in SQL Server). – Aaron Bertrand Jul 13 '11 at 19:40
  • Microsoft came up with INFORMATION_SCHEMA because that is part of the standard. Unfortunately, because they have added welcome features that don't fit into the standard views, the catalog views make a lot more sense for any objects where features are being developed (such as indexes). While I could name a bunch of things I can get from the catalog views that I can't get from the INFORMATION_SCHEMA views, I don't know of any data I can get from an INFORMATION_SCHEMA view that I can't get from a catalog view. Can you name anything? – Aaron Bertrand Jul 13 '11 at 19:42
  • You made your point -- I agree that sys views have more information in them than information schema does. Not relevant to this question, but true nonetheless. – Chains Jul 13 '11 at 19:42
1
DECLARE @TableName varchar(128)
DECLARE @IndexName varchar(128)
DECLARE @Command varchar(1000)

SET @TableName = 'PS_userVariables'

SELECT @IndexName = si.name
FROM sys.tables st
JOIN sys.indexes si ON st.object_id = si.object_id
WHERE st.name = @TableName
  AND si.is_primary_key = 1

SET @Command = 'ALTER TABLE dbo.' + QUOTENAME(@Tablename) + ' DROP CONSTRAINT ' + QUOTENAME(@IndexName) + ';
ALTER TABLE dbo.' + QUOTENAME(@Tablename) + ' ADD PRIMARY KEY (varnumber, subjectID, userID, datasetID, listid, userVarTitle);'
bobs
  • 21,346
  • 12
  • 63
  • 76