MS-SQL] 테이블 스키마 생성 추출 쿼리.

 

오늘 두개의 데이타 베이스의 스키마 비교 쿼리를 만들다 보니..

 

한쪽은 있고 다른 한쪽은 없을 때를 위해 테이블을 복제를 자동화 하려고 했는데...

 

쿼리 한방에 안되네! 

 

  SELECT * INTO 대상DB.dbo.테이블명 FROM 소스DB.dbo.테이블명 where 1 = 2

 

하면 복제는 되는데 키가 복제가 안됨을 확인.

 

 그래서 만들었다. 우선 mssql 스튜디오에서 만들어지는 스크립트와 같이 만들어 보고

 

고정 타입은 아닌것 같은데 군데 군데 하드 코딩이 되있어서 전혀 다르게 동작 가능.

 

음.. ms-sql에서 사용하는 테이블 -> Create 스크립트 쿼리 같은걸 이용하는건 못찾았다.

 

 

declare @tableName nvarchar(775) =QUOTENAME( SCHEMA_NAME() )+'.'+ QUOTENAME(  테이블명  )
declare @Script varchar(max) = ''
declare @FKScript varchar(max) = ''
declare @ctrl varchar(2) = char(13)

declare @type1 varchar(100) = 'char, varchar'
declare @type2 varchar(100) = 'nchar, nvarchar '
declare @type3 varchar(100) = 'numeric,decimal'
 
set @Script = 'CREATE TABLE ' + @tableName + '(' + @ctrl

-- Columns
select @Script += ' '+ QUOTENAME( c.name ) + ' '+ QUOTENAME( TYPE_NAME( system_type_id ) )
  + case
    when CHARINDEX( TYPE_NAME(system_type_id), @type1 , 0) > 0 then '('+ case when c.max_length = -1 then

'max' else CAST( c.max_length as varchar(20) ) end +')'
    when CHARINDEX( TYPE_NAME(system_type_id), @type2 , 0) > 0 then '('+ case when c.max_length = -1 then

'max' else CAST( c.max_length/2 as varchar(20) ) end +')'
    when CHARINDEX( TYPE_NAME(system_type_id), @type3 , 0) > 0 then '('+ CAST( c.precision as varchar(20) )

+ ',' + CAST( c.scale as varchar(20) )+')'
    else '' end
  + ' '+  case when is_nullable = 1 then '' else 'NOT' end + ' NULL,' + @ctrl
from sys.tables t inner join sys.columns c
on  t.object_id = c.object_id
where t.object_id = OBJECT_ID( @tableName )

-- 콤마 제거
set @Script = left( @Script , datalength( @Script )-  datalength( @ctrl ) - datalength( ',') ) + @ctrl

-- Primary Key 생성
select @Script += ' CONSTRAINT ' + QUOTENAME( name ) + ' PRIMARY KEY CLUSTERED '+ @ctrl
from sys.key_constraints
where parent_object_id = object_id( @tableName ) and [TYPE] = 'PK'

select @Script += ' (' + @ctrl

select  @Script += '  '+ QUOTENAME( c.name )+ ' ASC,' + @ctrl
from sys.index_columns i inner join sys.key_constraints k
on  i.object_id = k.parent_object_id
inner join sys.columns c
on  i.object_id = c.object_id and i.column_id = c.column_id
inner join sys.indexes ii
on  i.object_id = ii.object_id and i.index_id = ii.index_id
where parent_object_id = object_id( @tableName ) and k.[TYPE] = 'PK'
 
-- 콤마 제거
set @Script = left( @Script , datalength( @Script )-  datalength( @ctrl ) - datalength( ',') ) + @ctrl
 
select @Script += ' ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' + @ctrl
select @Script += ') ON [PRIMARY]' + @ctrl
select @Script += 'GO' + @ctrl

-- Foreign Key 추가
select @FKScript += 'ALTER TABLE '+QUOTENAME( SCHEMA_NAME() )+'.'+ QUOTENAME( OBJECT_NAME( fkeyid )) + ' WITH CHECK ADD 

CONSTRAINT '+ QUOTENAME( OBJECT_NAME( constid ) ) + ' FOREIGN KEY ('+  QUOTENAME( cf.name ) + ')'+ @ctrl
  + 'REFERENCES '+QUOTENAME( SCHEMA_NAME() )+'.'+ QUOTENAME( OBJECT_NAME( rkeyid )) + ' ('+ QUOTENAME(

cr.name )+')'+ @ctrl
  + 'GO' + @ctrl + @ctrl
  + case when parent_object_id is null then 'ALTER TABLE '+QUOTENAME( SCHEMA_NAME() )+'.'+ QUOTENAME(

OBJECT_NAME( fkeyid )) + ' CHECK CONSTRAINT '+ QUOTENAME( OBJECT_NAME( constid ) ) + 'GO' + @ctrl + @ctrl else '' end
from sys.sysforeignkeys f
left
outer join sys.columns cf
on  f.fkeyid = cf.object_id and f.fkey = cf.column_id
left
outer join sys.columns cr
on  f.rkeyid = cr.object_id and f.rkey = cr.column_id

left    -- CHECK KEY < 참조키만 된 컬럼 >
outer join
(
 select  k.parent_object_id, i.column_id
 from sys.index_columns i inner join sys.key_constraints k
 on i.object_id = k.parent_object_id
 and i.object_id = OBJECT_ID( @tableName )     
) t
on  f.fkeyid  = t.parent_object_id  and f.fkey = t.column_id

where fkeyid = OBJECT_ID( @tableName )

 

print @Script
print @FKScript