■ 특정 테이블에서 NULL 값만 가진 컬럼을 찾는 방법을 보여준다.
▶ 특정 테이블에서 NULL 값만 가진 컬럼 찾기 예제 (SQL)
|
1 2 3 |
EXECUTE GetEntireNullFieldList 'PhoneBook'; |
▶ 특정 테이블에서 NULL 값만 가진 컬럼 찾기 (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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
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 |