블로그 이미지
bedbmsguru

Notice

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        
  • 28,005total
  • 6today
  • 6yesterday
2010.11.03 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
2010.11.03 11:33 SQL SERVER

DECLARE cIX CURSOR FOR
   SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
      FROM Sys.Indexes SI
         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
      WHERE TC.CONSTRAINT_NAME IS NULL
         AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
      ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000) --SET @PKSQL = ''
   SET @IXSQL = 'CREATE '

   -- Check if the index is unique
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
      SET @IXSQL = @IXSQL + 'UNIQUE '
   -- Check if the index is clustered
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

   -- Get all columns of the index
   DECLARE cIxColumn CURSOR FOR
      SELECT SC.Name
      FROM Sys.Index_Columns IC
         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
      WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
      ORDER BY IC.Index_Column_ID

   DECLARE @IxColumn SYSNAME
   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN cIxColumn
   FETCH NEXT FROM cIxColumn INTO @IxColumn
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF (@IxFirstColumn = 1)
         SET @IxFirstColumn = 0
      ELSE
         SET @IXSQL = @IXSQL + ', '

      SET @IXSQL = @IXSQL + @IxColumn

      FETCH NEXT FROM cIxColumn INTO @IxColumn
   END
   CLOSE cIxColumn
   DEALLOCATE cIxColumn

   SET @IXSQL = @IXSQL + ')'
   -- Print out the CREATE statement for the index
   PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX
저작자 표시
신고
posted by bedbmsguru
2010.11.03 11:33 SQL SERVER

declare foreignkey_make_cursor cursor for
    select name, parent_object_id, referenced_object_id, delete_referential_action, update_referential_action from sys.foreign_keys

declare @foreignkey_name as varchar(100)
declare @current_table_id as int
declare @refrenced_table_id as int
declare @delete_refrencial_action as tinyint
declare @update_refrencial_action as tinyint

open foreignkey_make_cursor
fetch next from foreignkey_make_cursor    into @foreignkey_name, @current_table_id, @refrenced_table_id, @delete_refrencial_action, @update_refrencial_action
while (@@fetch_status = 0)
begin
    declare @result_query  as varchar(3000)
    set @result_query ='alter table ' + object_name(@current_table_id)
    set @result_query = @result_query  + ' add constraint ' + @foreignkey_name + ' foreign key '
    set @result_query = @result_query  + '('
   
    --foreign key 에  들어갈 column 을 불러오는 커서
    declare process_column_cursor cursor for
        select b.name from sys.foreign_key_columns as a
        inner join sys.columns as b
        on a.parent_object_id = b.object_id
        and a.parent_column_id = b.column_id
        where parent_object_id = @current_table_id

        declare @fkcolumn sysname
        declare @fkfirstcolumn bit set @fkfirstcolumn = 1

        open process_column_cursor
        fetch next from process_column_cursor into @fkcolumn
        while (@@fetch_status = 0)
        begin
            if (@fkfirstcolumn = 1)
                set @fkfirstcolumn = 0
            else
                set @result_query = @result_query + ', '

            set @result_query = @result_query + @fkcolumn

            fetch next from process_column_cursor into @fkcolumn
        end
        close process_column_cursor
        deallocate process_column_cursor
    --###########process_column_cursor 커서의 끝###############################
          
    set @result_query = @result_query  + ') '
    set @result_query = @result_query  + 'references ' + object_name(@refrenced_table_id) +'('
   
    --원본테이블에서 참조되고 있는 column 을 불러오는 커서   
    declare process_rdcolumn_cursor cursor for
        select b.name from sys.foreign_key_columns as a
        inner join sys.columns as b
        on a.referenced_object_id= b.object_id
        and a.referenced_column_id = b.column_id
        where referenced_object_id = @refrenced_table_id

        declare @rdcolumn sysname
        declare @rdfirstcolumn bit set @rdfirstcolumn = 1

        open process_rdcolumn_cursor
        fetch next from process_rdcolumn_cursor into @rdcolumn
        while (@@fetch_status = 0)
        begin
            if (@rdfirstcolumn = 1)
                set @rdfirstcolumn = 0
            else
                set @result_query = @result_query + ', '

            set @result_query = @result_query + @rdcolumn
            fetch next from process_rdcolumn_cursor into @rdcolumn
        end
    close process_rdcolumn_cursor
    deallocate process_rdcolumn_cursor
    --###########process_rdcolumn_cursor 커서의 끝###############################

    set @result_query = @result_query  + ')'
   
    if(@delete_refrencial_action = 0)
        set @result_query = @result_query + ' on delete no action'
    else if(@delete_refrencial_action = 1)
        set @result_query = @result_query + ' on delete cascade'
    else if(@delete_refrencial_action = 2)
        set @result_query = @result_query + ' on delete set null'
    else if(@delete_refrencial_action = 3)
        set @result_query = @result_query + ' on delete set default'
   
       
       
       
    if(@update_refrencial_action = 0)
        set @result_query = @result_query + ' on update no action'
    else if(@update_refrencial_action = 1)
        set @result_query = @result_query + ' on update cascade'
    else if(@update_refrencial_action = 2)
        set @result_query = @result_query + ' on update set null'
    else if(@update_refrencial_action = 3)
        set @result_query = @result_query + ' on update set default'
   
    print @result_query
    fetch next from foreignkey_make_cursor    into @foreignkey_name, @current_table_id, @refrenced_table_id, @delete_refrencial_action, @update_refrencial_action
end
close foreignkey_make_cursor
deallocate foreignkey_make_cursor
저작자 표시
신고
posted by bedbmsguru

티스토리 툴바