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
'SQL SERVER' 카테고리의 다른 글
SQL SERVER Function (0) | 2010.11.03 |
---|---|
Log Shipping 잘 되고 있는지 체크용 쿼리 (0) | 2010.11.03 |
디비의 테이블에 있는 모든 Non Clustered Index생성 스크립트 생성 커서 (0) | 2010.11.03 |
디비의 테이블에 있는 모든 Foreign KEY 생성 스크립트 생성 커서 (0) | 2010.11.03 |
SQL SERVER Performance 체크 항목 (0) | 2010.11.03 |