출처1 : http://msdn.microsoft.com/ko-kr/library/ms190324.aspx
출처2 : http://blog.danggun.net/1889
sp_defobject_v2.6.sql-- dbo.SysObjects.type desc
select * from master..spt_values where type = 'O9T'
개체 유형:
AF 집계 합수 (CLR)
C CHECK 제약 조건
D DEFAULT(제약 조건 또는 독립 실행형)
F FOREIGN KEY 제약 조건
FN SQL 스칼라 함수
FS 어셈블리(CLR) 스칼라 함수
FT 어셈블리(CLR) 테이블 반환 함수
IF SQL 인라인 테이블 반환 함수
IT 내부 테이블
P SQL 저장 프로시저
PC 어셈블리(CLR) 저장 프로시저
PG 계획 지침
PK PRIMARY KEY 제약 조건
R 규칙(이전 스타일, 독립 실행형)
RF 복제 필터 프로시저
S 시스템 기본 테이블
SN 시퀀스 개체
SO 시퀀스 개체
SQ 서비스 큐
TA 어셈블리(CLR) DML 트리거
TF SQL 테이블 반환 함수
TR SQL DML 트리거
TT CHECK 제약 조건
U 테이블(사용자 정의)
UQ UNIQUE 제약 조건
V 뷰
X 확장 저장 프로시저
이걸 약간 응용하여 아래와 같은 프로시저를 만들었다.
아래 프로시저를 생성한 뒤 다음과 같이 단축키 지정을 하면 편하게 사용할 수 있다.
나는 Ctrl + 5 번에 할당하였다.
2013/05/31 - [프로그램 자료/MS-SQL] - MSSQL 단축키 설정
use master
go
drop proc sp_defobject
go
create PROCEDURE sp_defobject
(
@objname NVARCHAR(500)
)
AS
BEGIN
set nocount on
if @objname IS NULL RETURN -1
set @objname = replace(@objname, '[dbo].', '');
set @objname = replace(@objname, '[', '');
set @objname = replace(@objname, ']', '');
declare @sql nvarchar(max), @params nvarchar(200), @rowcnt int, @temptable nvarchar(500), @spid int
declare @dbname sysname
select @dbname = db_name()
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
print(@id)
if @type is null
begin
--인덱스인지
set @sql = 'select @rowcnt = count(*) from sys.indexes where name = @xobjname'
set @params = '@xobjname nvarchar(500), @rowcnt int output'
exec SP_EXECUTESQL @sql, @params, @objname, @rowcnt output
if @rowcnt > 0
begin
exec sp_defindex @objname
end
else
begin
set @sql = 'select top 100 * from ' + @objname
Execute(@sql)
end
end
else if @type = 'U'
begin
Execute('select top 100 * from '+ @objname)
exec sp_help @objname
end
else
if @type = 'V'
begin
exec ('select top 100 * from '+ @objname )
set @sql =
'
select name, definition
from sys.objects o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = @xid and o.type = ''V''
'
set @params = '@xid int'
print(@sql)
exec SP_EXECUTESQL @sql, @params, @id;
end
else if @type in ('PK', 'K')
begin
exec sp_defindex @objname
end
else if @type = 'D'
begin
-- constraint 제약조건
set @sql =
'
SELECT dc.name,
''ALTER TABLE dbo.'' + OBJECT_NAME(parent_object_id) +
'' ADD CONSTRAINT '' + dc.name + '' DEFAULT('' + definition
+ '') FOR '' + c.name as definition
FROM sys.default_constraints dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
and dc.name = @xobjname
'
set @params = '@xobjname nvarchar(500)'
print(@sql)
exec SP_EXECUTESQL @sql, @params, @objname;
end
else if @type = 'F'
begin
set @sql =
'
DECLARE @l varchar(1) = char(13);
SELECT
fk.name AS ForeignKey,
OBJECT_NAME(fk.parent_object_id) AS TableName,
COL_NAME(fkc.parent_object_id,
fkc.parent_column_id) AS ColumnName,
OBJECT_NAME (fk.referenced_object_id) AS ReferenceTableName,
COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id) AS ReferenceColumnName,
''ALTER TABLE [dbo].[''+OBJECT_NAME(fk.parent_object_id)+''] DROP CONSTRAINT [''+fk.name+''];'' DropFK,
''ALTER TABLE [dbo].[''+OBJECT_NAME(fk.parent_object_id)+''] WITH NOCHECK ADD CONSTRAINT [''+fk.name+''] FOREIGN KEY([''+COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id)+''])''+ @l +
'' REFERENCES [dbo].[''+OBJECT_NAME(fk.referenced_object_id)+''] ([''+COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id)+''])''+ @l +
'' ON UPDATE CASCADE''+ @l +
'' ON DELETE CASCADE;''+ @l +
''ALTER TABLE [dbo].[''+OBJECT_NAME(fk.parent_object_id)+''] CHECK CONSTRAINT [''+fk.name+''];'' CreateFK
FROM
sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.OBJECT_ID = fkc.constraint_object_id
WHERE OBJECT_NAME(fk.object_id) = @xobjname
'
set @params = '@xobjname nvarchar(500)'
print(@sql)
exec SP_EXECUTESQL @sql, @params, @objname;
end
else
begin
set @sql =
'
SELECT A.type, A.NAME, b.definition
FROM dbo.SysObjects A LEFT OUTER JOIN sys.sql_modules B ON A.ID = B.object_id
WHERE A.id = object_id(@xobjname);
'
set @params = '@xobjname nvarchar(500)'
print(@sql)
exec SP_EXECUTESQL @sql, @params, @objname;
end
END
go
drop proc sp_defindex
go
create PROCEDURE sp_defindex
(
@objname NVARCHAR(500)
)
AS
BEGIN
declare @sql nvarchar(max), @params nvarchar(200)
set @sql =
'
SELECT
DB_NAME() AS database_name,
sc.name + N''.'' + t.name AS table_name,
(SELECT MAX(user_reads)
FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
last_user_update,
CASE si.index_id WHEN 0 THEN N''/* No create statement (Heap) */''
ELSE
CASE is_primary_key WHEN 1 THEN
N''ALTER TABLE '' + QUOTENAME(sc.name) + N''.'' + QUOTENAME(t.name) + N'' ADD CONSTRAINT '' + QUOTENAME(si.name) + N'' PRIMARY KEY '' +
CASE WHEN si.index_id > 1 THEN N''NON'' ELSE N'''' END + N''CLUSTERED ''
ELSE N''CREATE '' +
CASE WHEN si.is_unique = 1 then N''UNIQUE '' ELSE N'''' END +
CASE WHEN si.index_id > 1 THEN N''NON'' ELSE N'''' END + N''CLUSTERED '' +
N''INDEX '' + QUOTENAME(si.name) + N'' ON '' + QUOTENAME(sc.name) + N''.'' + QUOTENAME(t.name) + N'' ''
END +
N''('' + key_definition + N'')'' +
CASE WHEN include_definition IS NOT NULL THEN
N'' INCLUDE ('' + include_definition + N'')''
ELSE N''''
END +
CASE WHEN si.filter_definition IS NOT NULL THEN
N'' WHERE '' + si.filter_definition ELSE N''''
END +
N'' WITH ( ''+
CASE WHEN si.is_padded = 1 THEN '' PAD_INDEX = ON '' ELSE '' PAD_INDEX = OFF '' END + '','' +
''FILLFACTOR = ''+CONVERT(CHAR(5),CASE WHEN si.Fill_factor = 0 THEN 100 ELSE si.Fill_factor END) + '','' +
''SORT_IN_TEMPDB = OFF '' + '','' +
CASE WHEN si.ignore_dup_key = 1 THEN '' IGNORE_DUP_KEY = ON '' ELSE '' IGNORE_DUP_KEY = OFF '' END + '','' +
CASE WHEN ST.no_recompute = 0 THEN '' STATISTICS_NORECOMPUTE = OFF '' ELSE '' STATISTICS_NORECOMPUTE = ON '' END + '','' +
'' DROP_EXISTING = ON '' + '','' +
'' ONLINE = OFF '' + '','' +
CASE WHEN si.allow_row_locks = 1 THEN '' ALLOW_ROW_LOCKS = ON '' ELSE '' ALLOW_ROW_LOCKS = OFF '' END + '','' +
CASE WHEN si.allow_page_locks = 1 THEN '' ALLOW_PAGE_LOCKS = ON '' ELSE '' ALLOW_PAGE_LOCKS = OFF '' END +
+ N'')''+
'' ON '' + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N'''')
ELSE psc.name + N'' ('' + partitioning_column.column_name + N'')''
END
+ N'';''
END AS index_create_statement,
si.index_id,
si.name AS index_name,
partition_sums.reserved_in_row_GB,
partition_sums.reserved_LOB_GB,
partition_sums.row_count,
stat.user_seeks,
stat.user_scans,
stat.user_lookups,
user_updates AS queries_that_modified,
partition_sums.partition_count,
si.allow_page_locks,
si.allow_row_locks,
si.is_hypothetical,
si.has_filter,
si.fill_factor,
si.is_unique,
ISNULL(pf.name, ''/* Not partitioned */'') AS partition_function,
ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
t.create_date AS table_created_date,
t.modify_date AS table_modify_date
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
JOIN sys.stats ST ON ST.object_id = si.object_id AND ST.stats_id = si.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N'' DESC'' ELSE N'''' END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS keys ( key_definition )
OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS includes ( include_definition )
OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS row_compression_clause ( row_compression_partition_list )
OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2)
and si.name = @xobjname
ORDER BY table_name, si.index_id
OPTION (RECOMPILE);
'
set @params = '@xobjname nvarchar(500)'
print(@sql)
exec SP_EXECUTESQL @sql, @params, @objname;
end
go
