프로시저 내용 검색

맨날 검색해서 찾다가 혹시나 해서 저장겸 기록.

프로시저 안의 문장을 검색할때 유용한 쿼리.

MS-SQL

select  a.name, b.text 
from    sys.sysobjects a 
        inner join sys.syscomments b on a.id = b.id
where   a.xtype = 'P'
and     a.category = 0
and     b.text like '%검색 문자열%'

select a.name, b.text

from sys.sysobjects a

inner join sys.syscomments b on a.id = b.id

where a.xtype = 'P'

and a.category = 0

and b.text like '%검색 문자열%'

ref. http://tennesseewaltz.tistory.com/trackback/218

테이블의 RowSize 등 테이블 요약 정보를 조회하는 쿼리

MS-SQL

-- 일부 테이블 조회
dbcc showcontig ('TableName') with tableresults
-- 전체 테이블 조회
dbcc showcontig () with tableresults

-- 일부 테이블 조회

dbcc showcontig ('TableName') with tableresults

-- 전체 테이블 조회

dbcc showcontig () with tableresults

ref. http://www.sqlserver-dba.com/2013/07/calculate-sql-row-size.html

테이블 컬럼명, DataType, Description, PK, FK 등의 정보를 출력하는 쿼리

select  a.table_name, c.value table_description, a.ordinal_position, a.column_name, b.value colum_description, a.data_type, 
        isnull(cast(a.character_maximum_length as varchar), cast(a.numeric_precision as varchar) + ',' + cast(a.numeric_scale as varchar)) column_length,
        a.is_nullable, a.column_default
from    information_schema.columns a
        left join   sys.extended_properties b  
          on          b.major_id      = object_id(a.table_name)   
          and         b.minor_id      = a.ordinal_position  
        left join   (select  object_id(objname) table_id,value  
                    from    ::fn_listextendedproperty(null, 'user','dbo','table',null, null, null)) c  
          on          c.table_id  = object_id(a.table_name)
order by a.table_name, a.ordinal_position



select  pk.table_name, c.column_name, pk.constraint_type, pk.constraint_name, pk.*
from    information_schema.table_constraints pk,
        information_schema.key_column_usage c
where   pk.CONSTRAINT_TYPE = 'FOREIGN KEY'
and     c.table_name = pk.table_name
and     c.constraint_name = pk.constraint_name
order by pk.constraint_type, pk.table_name


select  fk.table_name, cu.column_name, pk.table_name, pt.column_name, c.constraint_name 
from    information_schema.referential_constraints c 
        inner join  information_schema.table_constraints fk 
          on  c.constraint_name = fk.constraint_name
        inner join  information_schema.table_constraints pk
          on  c.unique_constraint_name = pk.constraint_name 
        inner join  information_schema.key_column_usage cu 
          on  c.constraint_name = cu.constraint_name
        inner join  (select i1.table_name, i2.column_name 
                    from    information_schema.table_constraints i1 
                            inner join  information_schema.key_column_usage i2
                              on  i1.constraint_name = i2.constraint_name 
                    where   i1.constraint_type = 'PRIMARY KEY') pt 
          on  pt.table_name = pk.table_name 
-- optional: 
order by  1,2,3,4

select a.table_name, c.value table_description, a.ordinal_position, a.column_name, b.value colum_description, a.data_type,

isnull(cast(a.character_maximum_length as varchar), cast(a.numeric_precision as varchar) + ',' + cast(a.numeric_scale as varchar)) column_length,

a.is_nullable, a.column_default

from information_schema.columns a

left join sys.extended_properties b

on b.major_id = object_id(a.table_name)

and b.minor_id = a.ordinal_position

left join (select object_id(objname) table_id,value

from ::fn_listextendedproperty(null, 'user','dbo','table',null, null, null)) c

on c.table_id = object_id(a.table_name)

order by a.table_name, a.ordinal_position

select pk.table_name, c.column_name, pk.constraint_type, pk.constraint_name, pk.*

from information_schema.table_constraints pk,

information_schema.key_column_usage c

where pk.CONSTRAINT_TYPE = 'FOREIGN KEY'

and c.table_name = pk.table_name

and c.constraint_name = pk.constraint_name

order by pk.constraint_type, pk.table_name

select fk.table_name, cu.column_name, pk.table_name, pt.column_name, c.constraint_name

from information_schema.referential_constraints c

inner join information_schema.table_constraints fk

on c.constraint_name = fk.constraint_name

inner join information_schema.table_constraints pk

on c.unique_constraint_name = pk.constraint_name

inner join information_schema.key_column_usage cu

on c.constraint_name = cu.constraint_name

inner join (select i1.table_name, i2.column_name

from information_schema.table_constraints i1

inner join information_schema.key_column_usage i2

on i1.constraint_name = i2.constraint_name

where i1.constraint_type = 'PRIMARY KEY') pt

on pt.table_name = pk.table_name

-- optional:

order by 1,2,3,4

ref. 기억 안남…

프로시저 내용 검색

답글 남기기 응답 취소