MsSQL 관련 메모

1. 전체 테이블명

SELECT * FROM information_schema.TABLE_CONSTRAINTS;

2. 전체테이블의 컬럼명

SELECT * FROM information_schema.Columns

3. 테이블별 PK 정보

SELECT * FROM information_schema.KEY_COLUMN_USAGE

4. LAST_INSERT_ID

SELECT SCOPE_IDENTITY() AS last_insert_id;

5. index 목록

SELECT
        TableName = t.name,
        IndexName = ind.name,
        IndexId = ind.index_id,
        ColumnId = ic.index_column_id,
        ColumnName = col.name,
    ind.*,
    ic.*,
    col.*
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
ORDER BY
    ind.name, t.name, ind.index_id, ic.index_column_id

6. ???

SELECT
    SC.DisplayName,
    SR.SearchCategory,
    CoverageType = STUFF((
         SELECT ',' + SR1.CoverageType
         FROM tblSearchReq SR1 WHERE SR.SearchCategory=SR1.SearchCategory AND SR1.CoverageType <> 'Nationwide'
         GROUP BY SR1.SearchCategory, SR1.CoverageType
         FOR XML PATH(''), TYPE).value('.', 'VARCHAR(100)'), 1, 1, ''
    )
FROM
    SearchCategory SC
    INNER JOIN tblSearchReq SR ON SC.CategoryName=SR.SearchCategory
WHERE
    SR.CoverageType <> 'Nationwide'
GROUP BY
    SC.DisplayName, SR.SearchCategory
ORDER BY
    SC.DisplayName