테이블 정보 조회

  

DECLARE @TABLE_NAME nvarchar(100) SET @TABLE_NAME = 'Board' --테이블 컬럼 정보 SELECT ORDINAL_POSITION , COLUMN_NAME , ISNULL(( SELECT P.VALUE FROM :: FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'DBO', 'TABLE', TABLE_NAME, DEFAULT, DEFAULT)p), '') , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , IS_NULLABLE , COLUMN_DEFAULT , ISNULL(( SELECT VALUE FROM :: FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'DBO', 'TABLE', TABLE_NAME, 'COLUMN', COLUMN_NAME)Q), '') FROM INFORMATION_SCHEMA.COLUMNS --WHERE TABLE_NAME = @TABLE_NAME --테이블 키 하나줄로 보기 SELECT I.NAME , REPLACE('1' + STUFF((SELECT ' + ' + AC1.NAME + CASE WHEN IC1.IS_DESCENDING_KEY = '0' THEN '(오름차순)' ELSE '(내림차순)' END AS [text()] FROM SYS.TABLES T1 LEFT OUTER JOIN SYS.INDEXES I1 ON T1.OBJECT_ID = I1.OBJECT_ID LEFT OUTER JOIN SYS.INDEX_COLUMNS IC1 ON IC1.OBJECT_ID = I1.OBJECT_ID AND IC1.INDEX_ID = I1.INDEX_ID LEFT OUTER JOIN SYS.ALL_COLUMNS AC1 ON IC1.OBJECT_ID = AC1.OBJECT_ID AND IC1.COLUMN_ID = AC1.COLUMN_ID WHERE T1.NAME = T.NAME AND I1.TYPE_DESC = 'CLUSTERED' FOR XML PATH('')) , 1, 1, '' ) , '1+ ', '') , AC.NAME + CASE WHEN IC.IS_DESCENDING_KEY = '0' THEN '(오름차순)' ELSE '(내림차순)' END , IC.INDEX_ID , IC.INDEX_COLUMN_ID , I.TYPE_DESC FROM SYS.TABLES T LEFT OUTER JOIN SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID LEFT OUTER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = I.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID LEFT OUTER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID AND IC.COLUMN_ID = AC.COLUMN_ID WHERE I.TYPE_DESC = 'CLUSTERED' --AND T.NAME = @TABLE_NAME ORDER BY IC.INDEX_ID, T.NAME, I.NAME --테이블 키, 인덱스 보기 SELECT I.NAME , AC.NAME + CASE WHEN IS_DESCENDING_KEY = '0' THEN '(오름차순)' ELSE '(내림차순)' END , IC.INDEX_ID , IC.INDEX_COLUMN_ID , I.TYPE_DESC , T.NAME FROM SYS.TABLES T LEFT OUTER JOIN SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID LEFT OUTER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = I.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID LEFT OUTER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID AND IC.COLUMN_ID = AC.COLUMN_ID --WHERE T.NAME =@TABLE_NAME ORDER BY IC.INDEX_ID, T.NAME, I.NAME

테이블 컬럼 정보 조회하기

--컬럼 주석 SELECT object_name(major_id) , col_Name(major_id, minor_id) , value FROM sys.extended_properties WHERE minor_id>0 AND class=1 AND object_name(major_id) = 'DDETB_Z1_StndItemCost' -- table 커멘트 SELECT object_name(major_id) , value FROM sys.extended_properties WHERE minor_id=0 AND class=1

--MSSQL에서 DB목록, Table목록, 그리고 각 Table의 상세 칼럼 정보를 얻어오기 SELECT * FROM SYS.SYSDATABASES SELECT * FROM SYS.TABLES SELECT * FROM SYS.SYSCOLUMNS SELECT * FROM SYS.SYSTYPES SELECT A.NAME AS TABLE_NAME, B.NAME AS COLUMN_NAME, C.NAME AS DATA_TYPE, C.LENGTH AS DATA_LENGTH FROM SYS.TABLES A JOIN SYS.SYSCOLUMNS B ON A.OBJECT_ID=B.ID JOIN SYS.SYSTYPES C ON C.XTYPE=B.XTYPE WHERE A.NAME = 'TableName' --테이블이름 ORDER BY TABLE_NAME  

--테이블 컬럼 조회 SELECT A.name AS TblNm , B.name AS ColNm FROM SYS.TABLES A LEFT OUTER JOIN SYS.SYSCOLUMNS B ON A.object_id = B.id where A.name = 'Info'  

-- 현재 서버의 DataBase 정보(이름, 사이즈 등의 정보를 조회) Select * from Sys.Databases EXEC sp_databases EXEC sp_helpdb --테이블 조회 SELECT name , type_desc , create_date , modify_date FROM SYS.TABLES ORDER BY name