맨날 검색해서 찾다가 혹시나 해서 저장겸 기록.
프로시저 안의 문장을 검색할때 유용한 쿼리.
MS-SQL
1 2 3 4 5 6 |
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
1 2 3 4 |
-- 일부 테이블 조회 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 등의 정보를 출력하는 쿼리
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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. 기억 안남…