T_SQL::CONSTRAINT조사

CONSTRAINT 조사

 

1. select * from sys.default_constraints 
2. select * from sys.key_constraints 
3. select * from sys.check_constraints 
4. 
   
5. --=================
6. -- 2005용
7. --=================
8. -- FK
9. SELECT  object_name(fk.constraint_object_id) AS fk_name, 
10.     fk.constraint_column_id AS fk_clolum,
11.     object_name(fk.parent_object_id) AS parent_name,
12.     (select name from sys.columns where object_id =  fk.parent_object_id and column_id = fk.parent_column_id) as parent_column,
13.     object_name(fk.referenced_object_id) AS referenced_name,
14.     (select name from sys.columns where object_id =  fk.referenced_object_id and column_id = fk.referenced_column_id) as referenced_column
15. FROM sys.foreign_key_columns  as fk
16. WHERE fk.parent_object_id  = object_id('<@table_name, @sysname,@table_name>') 
17.         or  fk.referenced_object_id = object_id('<@table_name, @sysname,@table_name>')
18. 
    
19. -- DEFAULT
20. SELECT object_name(parent_object_id) as table_name,
21.        name,
22.        (select name from sys.columns where object_id =  df.parent_object_id and column_id = df.parent_column_id) as column_name, 
23.        definition
24. FROM sys.default_constraints   as df
25. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
26. 
    
27. -- DEFAULT
28. SELECT
29.     object_name(parent_object_id) as table_name,
30.        name,
31.        (select name from sys.columns where object_id =  ck.parent_object_id and column_id = ck.parent_column_id) as column_name, 
32.        definition
33. FROM sys.check_constraints  as ck
34. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
35. 
    
36. 
    
37. 
    
38. --=================
39. -- 2000용
40. --=================
41. --FK
42. select object_name(fkeyid) , object_name(constid),object_name(rkeyid)  
43. from sys.sysforeignkeys where fkeyid = object_id ('<@table_name, @sysname,@table_name>')
44. 
    
45. 
    
46. select object_name(id), object_name(constid) ,status from sys.sysconstraints  
47. where status&5 = 1 --FK 
48. where constid  = object_id('<@table_name, @sysname,@table_name>')
49. order by object_name(constid)
50. 
    
51. 
    
52. 
    
53. --=========================== 
54. -- DROP
55. --===========================
56. -- FK 2005용
57. SELECT 'ALTER TABLE ' + bject_name(fk.parent_object_id) + ' DROP CONSTRAINT ' +  object_name(fk.constraint_object_id)
58. FROM sys.foreign_key_columns
59. WHERE fk.parent_object_id  = object_id('<@table_name, @sysname,@table_name>') 
60. 
    
61. -- DEFULT 2005용
62. SELECT 'ALTER TBLE ' + object_name(parent_object_id)  + ' DROP CONSTRAINT ' + name
63. FROM sys.default_constraints 
64. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
65. 
    
66. -- CK 2005용
67. -- DEFULT 2005용
68. SELECT 'ALTER TBLE ' + object_name(parent_object_id)  + ' DROP CONSTRAINT ' + name
69. FROM sys.default_constraints  
70. WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
71. 
    
72. 
    
73. -- FK 2000용
74. SELECT 'ALTER TABLE ' + object_name(fkeyid) + ' DROP CONSTRAINT ' +  object_name(constid) 
75. FROM sys.sysforeignkeys
76. WHERE fkeyid = object_id ('<@table_name, @sysname,@table_name>')
77. 
    
78. -- INDEX 2000용
79. select  'ALTER TABLE ' + object_name(id) + ' DROP INDEX '  + name 
80. from sys.sysindexes where  object_name(id) in ('GOODSDAQ_BANNER_POSITION')
81. order by id, name
82. 
    

    

        

이 글은 스프링노트에서 작성되었습니다.