■ 데이터베이스 제약 조건 컬럼을 조회하는 방법을 보여준다.
▶ 예제 코드 (SQL)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SELECT A.CONSTRAINT_CATALOG AS ConstraintDatabase ,A.CONSTRAINT_SCHEMA AS ConstraintSchema ,A.CONSTRAINT_NAME AS [Constraint] ,A.TABLE_CATALOG AS [Database] ,A.TABLE_SCHEMA AS [Schema] ,A.TABLE_NAME AS [Table] ,CONVERT(INT, B.ORDINAL_POSITION) AS Sequence ,A.COLUMN_NAME AS [Column] ,UPPER(B.DATA_TYPE) AS DataType ,ISNULL(B.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize ,ISNULL(B.CHARACTER_OCTET_LENGTH, 0) AS ByteSize ,ISNULL(B.NUMERIC_PRECISION, 0) AS NumericPrecision ,ISNULL(B.NUMERIC_PRECISION_RADIX, 0) AS NumericPrecisionRadix ,ISNULL(B.NUMERIC_SCALE, 0) AS NumericScale ,ISNULL(B.DATETIME_PRECISION, 0) AS DateTimePrecision ,CASE B.IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE '' END AS [NULL] ,( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END FROM syscolumns AS C JOIN sysobjects AS D ON D.id = C.id WHERE C.status = 128 AND D.xtype = 'U' AND D.name = B.TABLE_NAME AND C.name = B.COLUMN_NAME ) AS IsIdentity ,ISNULL(B.COLUMN_DEFAULT, '') AS [Default] ,ISNULL(B.CHARACTER_SET_CATALOG, '') AS CharacterSetCatalog ,ISNULL(B.CHARACTER_SET_SCHEMA, '') AS CharacterSetSchema ,ISNULL(B.CHARACTER_SET_NAME, '') AS CharacterSetName ,ISNULL(B.COLLATION_CATALOG, '') AS CollationCatalog ,ISNULL(B.COLLATION_NAME, '') AS [CollationName] ,ISNULL(B.DOMAIN_CATALOG, '') AS DomainCatalog ,ISNULL(B.DOMAIN_SCHEMA, '') AS DomainSchema ,ISNULL(B.DOMAIN_NAME, '') AS DomainName FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS A JOIN INFORMATION_SCHEMA.COLUMNS AS B ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME ORDER BY A.TABLE_NAME ASC ,A.CONSTRAINT_NAME ASC ,B.ORDINAL_POSITION ASC; |