블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

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
  • total
  • today
  • yesterday
2010. 11. 3. 11:34 SQL SERVER

비슷한 일을 하는 쿼리를 만들어야 할때 참고할 것
혹시 이글 보시고 더 좋은 방법 아시는분은 알려주세요^^;;

DECLARE CHECK_COLUMN CURSOR FOR
select OBJECT_NAME(object_id) AS table_name, object_id  from sys.columns where system_type_id in(231, 167) and object_id > 100
grOUP BY object_id

DECLARE @table_name AS VARCHAR(100)
DECLARE @table_id AS BIGINT

OPEN CHECK_COLUMN
    FETCH NEXT FROM CHECK_COLUMN INTO @table_name, @table_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @RESULT_QUERY AS VARCHAR(2000)
       
        SET @RESULT_QUERY = 'SELECT TOP 1 '''  + @table_name + ''', '
        DECLARE PROCESS_COLUMN CURSOR FOR
            select name from sys.columns where system_type_id in(231, 167) and object_id = @table_id
           
        DECLARE @column_name AS VARCHAR(100)
        DECLARE @FkFirstColumn BIT SET @FkFirstColumn = 1
        OPEN PROCESS_COLUMN
        FETCH NEXT FROM PROCESS_COLUMN INTO @column_name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF(@FkFirstColumn = 1)
                SET @FkFirstColumn = 0
            ELSE
                SET @RESULT_QUERY = @RESULT_QUERY + ', '       
            SET @RESULT_QUERY = @RESULT_QUERY + @column_name
           
            FETCH NEXT FROM PROCESS_COLUMN INTO @column_name
        END
        CLOSE PROCESS_COLUMN
        DEALLOCATE PROCESS_COLUMN
    SET @RESULT_QUERY = @RESULT_QUERY + ' FROM ' + @table_name
    PRINT @RESULT_QUERY
    FETCH NEXT FROM CHECK_COLUMN INTO @table_name, @table_id
    END
CLOSE CHECK_COLUMN
DEALLOCATE CHECK_COLUMN
posted by bedbmsguru