메뉴 건너뛰기

kode2.com

MSSQL 컬럼 이름 변경

2019.02.16 15:57

kode2 조회 수:17369

DECLARE curj CURSOR 
FOR
SELECT name AS DBNAME FROM master..sysdatabases WHERE databasepropertyex(name, 'status')='ONLINE'
 
OPEN curj
declare @strSQL varchar(8000)
declare @DBNAME varchar(30)
declare @TableNm varchar(50)
declare @ColumnNm varchar(50)
declare @ColumnNm2 varchar(50)
 
 
set @TableNm='TB_Table'
set @ColumnNm='column1'
set @ColumnNm2='column2'
 
 
FETCH NEXT FROM curj INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @strSQL = 'USE ['+@DBNAME+']
IF EXISTS(SELECT id FROM sysobjects WHERE name='''+@TableNm+''')
BEGIN
IF NOT EXISTS(SELECT name FROM syscolumns 
WHERE name='''+@ColumnNm+''' and id=(SELECT id FROM sysobjects WHERE name='''+@TableNm+'''))
BEGIN
IF EXISTS(SELECT name FROM syscolumns 
WHERE name='''+@ColumnNm2+''' and id=(SELECT id FROM sysobjects WHERE name='''+@TableNm+'''))
BEGIN
print '''+@DBNAME+' '+@ColumnNm2+' 있음''
END
ELSE
BEGIN
print '''+@DBNAME+' 둘다 없음''
END
END
ELSE
BEGIN
print '''+@DBNAME+' '+@ColumnNm+'를 '+@ColumnNm2+'로 변경''
EXEC sp_rename '''+@TableNm+'.'+@ColumnNm+''', '''+@ColumnNm2+''', ''COLUMN''
END
END
ELSE
BEGIN
print '''+@DBNAME+' '+@TableNm+' 없음''
END
'
exec(@strSQL)
 
FETCH NEXT FROM curj INTO @DBNAME
END
 
CLOSE curj
DEALLOCATE curj