첨부 소스 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170

▶ 특정 테이블에서 NULL 값만 가진 컬럼 찾기 예제

EXECUTE GetEntireNullFieldList 'PhoneBook';

 

728x90

 

▶ 특정 테이블에서 NULL 값만 가진 컬럼 찾기

CREATE PROCEDURE dbo.GetEntireNullFieldList
(
    @P_TableName VARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL       VARCHAR(MAX);
    DECLARE @FieldName VARCHAR(30);
    DECLARE @Count     INT;

    SET @SQL   = '';
    SET @Count = 0;

    DECLARE Cursor1 CURSOR GLOBAL FOR
        SELECT
            B.[name] AS FieldName
        FROM
        (
            SELECT
                A.[name] AS TableName
            FROM sys.tables A
            WHERE A.[type] = 'U'
            AND   A.[name] = @P_TableName
        ) A
        LEFT OUTER JOIN sys.columns B ON Object_Name(B.Object_ID) = A.TableName
        ORDER BY
            A.TableName ASC,
            B.[name]    ASC;

    OPEN Cursor1;

    FETCH NEXT FROM Cursor1 INTO @FieldName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Count = @Count + 1;

        IF @Count != 1
        BEGIN
            SET @SQL = @SQL + ' UNION ALL' + CHAR(13);
        END

        SET @SQL = @SQL + 'SELECT ''' + @P_TableName + ''' AS TableName, ''' + @FieldName +
            ''' AS FieldName, COUNT(*) AS FieldCount' + CHAR(13);

        IF @Count = 1
        BEGIN
            SET @SQL = @SQL + ' INTO #Temp ' + CHAR(13);
        END

        SET @SQL = @SQL + ' FROM ' + @P_TableName + CHAR(13) + ' WHERE ' + @FieldName + ' IS NULL ' + CHAR(13);

        FETCH NEXT FROM Cursor1 INTO @FieldName;
    END

    CLOSE Cursor1;

    DEALLOCATE Cursor1;

    SET @SQL = @SQL + CHAR(13) +
        'SELECT TableName, FieldName FROM #Temp WHERE FieldCount = (SELECT COUNT(*) FROM ' + @P_TableName + ')';

    EXECUTE (@SQL);
END
GO
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요