[MS-SQL] 음... 잡다?

SQL을 다룰때마다 느끼는거지만... 참 재미있는게 많은데..

모르면 손가락 아프게 열심히 쳐야 한다는 사실...

항상 사용하는 것들 또 MSDN을 찾기 구찮아서 정리 해둔다..

아래 내용이면 테이블에 속성이나 자료형 길이등을 뽑아내서 스크립트화 가능한다... 

index_col( object_name( t.object_id ), i.index_id, c.column_id )  인덱스 컬럼인지 확인해준다.

type_name( c.system_type_id )  타입명을 반환

case 
when lower(type_name( c.system_type_id )) in ('char', 'varchar', 'nchar' ,'nvarchar', 'text' )  
then '('+convert( varchar(10), columnproperty( c.object_id, c.name, 'precision' ) )+')'       
when lower(type_name( c.system_type_id )) in ('numeric','decimal' ) 
then '('+ convert(varchar, c.precision) + ',' + convert(varchar, c.scale)+')'    
when lower(type_name( c.system_type_id )) in ('float','datetime', 'smalldatetime', 'tinyint', 'int', 'bigint' ) 
then ''           
else ''           
end  

요건 타입에 사용한 길이를 가져오는것인데...
원래 columnproperty( c.object_id, c.name, 'precision' )  이것인데...
잘 안된다.  총 길이를 자꾸 뱉어내서 정수형 INT, BIGINT같은건 길이가 나타남

 sys.tables  테이블들...

 sys.columns  컬럼들...

 sys.index_columns 인덱스컬럼들...

 sys.extended_properties  확장속성들...

 sys.foreign_key_columns  참조키 컬럼들...


DECLARE @TABLE_NAME NVARCHAR(776) = '테이블명'
 
select  [tablename]  = object_name( c.object_id ),
  [columnname] = c.name,
  [columndesc] = isnull(e.value, ''),
  [pk/fk]   = ltrim( rtrim( case
        when isnull( index_col( object_name( t.object_id ), i.index_id, c.column_id ),'') <> ''
        then 'pk'
        else ''
        end + '' +     
        case
        when isnull( f.constraint_column_id , '' ) <> ''
        then 'fk'
        else ''
        end )),
     [allowIsNull]  = isnull(  case when c.is_nullable = 0 then 'no' end , ''),
  [typename]  = type_name( c.system_type_id ),
  [columnlength] = case
        when lower(type_name( c.system_type_id )) in ('char', 'varchar', 'nchar' ,'nvarchar', 'text' ) 
        then '('+convert( varchar(10), columnproperty( c.object_id, c.name, 'precision' ) )+')'      
        when lower(type_name( c.system_type_id )) in ('numeric','decimal' )
        then '('+ convert(varchar, c.precision) + ',' + convert(varchar, c.scale)+')'   
        when lower(type_name( c.system_type_id )) in ('float','datetime', 'smalldatetime', 'tinyint', 'int', 'bigint' )
        then ''          
        else ''          
        end
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id -- and t.schema_id = 7
left outer join sys.index_columns i    
on c.object_id = i.object_id and c.column_id = i.column_id 
left outer join sys.extended_properties e         
 on c.object_id = e.major_id and c.column_id = e.minor_id and e.class = 1       
left outer join  sys.foreign_key_columns f     
on c.object_id = f.parent_object_id and c.column_id = f.parent_column_id 
WHERE @TABLE_NAME = case nullif( @TABLE_NAME, '') when '' then '' else OBJECT_NAME( t.object_id ) end  
order by t.name, c.is_nullable, c.name


이 구문이면 테이블정보를 쉽게 파악이 가능하고

확장속성에 스크립트를 걸어두면 속성이 컬럼명 옆에 붙어 나오므로 보기 더 좋음.

이걸 편집하면 테이블 스키마는 쉽게 뽑아낼 수 있음.

참고 # SP_DEPENDS 를 편집에서 나만의 프로시져로 만들어 사용하기도 한다..
각종 시스템 프로시져를 편집에서 사용이 편리한 놈으로 개조해서 사용하기도 함!!!