■ 데이타베이스 테이블 컬럼을 조회하는 방법을 보여준다.
▶ 예제 코드 (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 |
SELECT A.TABLE_CATALOG AS [Database] ,A.TABLE_SCHEMA AS [Schema] ,A.TABLE_NAME AS [Table] ,CONVERT(INT, A.ORDINAL_POSITION) AS Sequence ,A.COLUMN_NAME AS [Column] ,UPPER(A.DATA_TYPE) AS DataType ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize ,ISNULL(A.CHARACTER_OCTET_LENGTH, 0) AS ByteSize ,ISNULL(A.NUMERIC_PRECISION, 0) AS NumericPrecision ,ISNULL(A.NUMERIC_PRECISION_RADIX, 0) AS NumericPrecisionRadix ,ISNULL(A.NUMERIC_SCALE, 0) AS NumericScale ,ISNULL(A.DATETIME_PRECISION, 0) AS DateTimePrecision ,CASE A.IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE '' END AS [NULL] ,( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END FROM syscolumns AS B JOIN sysobjects AS C ON C.id = B.id WHERE B.status = 128 AND C.xtype = 'U' AND C.name = A.TABLE_NAME AND B.name = A.COLUMN_NAME ) AS IsIdentity ,ISNULL(A.COLUMN_DEFAULT, '') AS [Default] ,ISNULL(A.CHARACTER_SET_CATALOG, '') AS CharacterSetCatalog ,ISNULL(A.CHARACTER_SET_SCHEMA, '') AS CharacterSetSchema ,ISNULL(A.CHARACTER_SET_NAME, '') AS CharacterSetName ,ISNULL(A.COLLATION_CATALOG, '') AS CollationCatalog ,ISNULL(A.COLLATION_NAME, '') AS [CollationName] ,ISNULL(A.DOMAIN_CATALOG, '') AS DomainCatalog ,ISNULL(A.DOMAIN_SCHEMA, '') AS DomainSchema ,ISNULL(A.DOMAIN_NAME, '') AS DomainName FROM INFORMATION_SCHEMA.COLUMNS AS A WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE') ORDER BY A.TABLE_NAME ASC ,A.ORDINAL_POSITION ASC; |