SP_HELPX는 SQL Server가 기본 제공하는 프로시져는 아닙니다. 제가 자주 쓰는 시스템 저장 프로시저나 명령들을 하나로 합쳐서 만들었습니다. SP_HELPX는 하나의 파라메터를 가지고 있는데 오브젝트의 이름이나 인덱스 이름을 주면 됩니다. 오브젝트의 TYPE에 따라 각기 다른 기능을 제공합니다. 프로시저, 함수, 트리거의 경우는 소스를 보여줍니다. 체크, 디폴트, 룰의 경우는 정의한 소스와 어디에 쓰였는지를 보여줍니다. 포린키는 부모테이블 자식테이블을 보여줍니다. 테이블, 뷰의 경우는 좀 기능이 많은데 컬럼이름, 타입, 길이, NULL허용여부, IDENTITY정보, 계산된 열의 소스, 제약조건이 걸려있다면 이름과 소스를 보여줍니다. 그리고 인덱스가 있으면 인덱스 정보를 보여줍니다. SP_HELPINDEX는 포함된 열은 표시를 안해주는데 포함된 열도 표시해줍니다. 그리고 INDEX에 포함된 데이터 건수도 보여줍니다. 필터인덱스의 경우 유용합니다. 인덱스의 조각화 정보도 보여주는데 테이블이 클 경우 너무 느립니다. 그래서 똑같은 소스로 SP_HELPZ를 만들고 SP_HELPZ를 실행할때만 보여주도록 했습니다. 테이블에 포린키가 걸려있다면 부모테이블이나 자식테이블을 보여줍니다. 트리거가 있다면 트리거 이름을 보여줍니다. 마지막으로 테이블의 데이터 100건을 보여줍니다. 추가로 뷰의 경우는 소스를 보여줍니다. 테이블을 반환하는 펑션의 경우는 소스와 반환하는 테이블의 구조를 보여줍니다. 인덱스는 통계정보와 인덱스가 속한 테이블의 정보를 보여줍니다. 파라메터에 컬럼 이름이 오면 어떤 그 컬럼 이름을 사용하는 테이블 리스트를 보여주니다. 타입의 경우엔 어느 컬럼에 사용되었는지 보여줍니다. 파라메터에 #검색어# 이런식으로 주면 오브젝트, 컬럼, 인덱스, 사용자정의 타입 들을 검색해서 보여줍니다. 파라메터에 빈 문자열을 주면 자주 쓰는 명령어나 프로시저를 프린트 하도록 했습니다. SP_HELPX를 단축키로 등록해놓으면 아주아주아주 좋습니다. 방법은 SSMS의 도구>>옵션>>환경>>키보드>>쿼리 바로 가기에 등록해두면 됩니다. 다음은 SP_HELPX의 소스입니다.USE master GO IF OBJECT_ID('dbo.SP_HELPX', 'P') IS NULL BEGIN EXEC ('CREATE PROCEDURE dbo.SP_HELPX AS SELECT TODAY = GETDATE()') GRANT EXECUTE ON master.dbo.SP_HELPX TO [PUBLIC] END GO ALTER PROC SP_HELPX @OBJECT SYSNAME = '' , @OPTION NVARCHAR(128) = '' , @DEBUG INT = 0 AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON IF @OBJECT = '' BEGIN PRINT '/* SP_HELPX 21.1110.002 */' PRINT '/* Written By chozzahacker */' PRINT '/* https://chozzahacker.blogspot.kr */' PRINT '/* chozzahacker@gmail.com */' PRINT '' PRINT '/* Date And Time Statements */' PRINT 'SET DATEFIRST' PRINT 'SET DATEFORMAT' PRINT '' PRINT '/* Locking Statements */' PRINT 'SET DEADLOCK_PRIORITY' PRINT 'SET LOCK_TIMEOUT' PRINT '' PRINT '/* Miscellaneous Statements */' PRINT 'SET CONCAT_NULL_YIELDS_NULL' PRINT 'SET CURSOR_CLOSE_ON_COMMIT' PRINT 'SET FIPS_FLAGGER' PRINT 'SET IDENTITY_INSERT' PRINT 'SET LANGUAGE' PRINT 'SET OFFSETS' PRINT 'SET QUOTED_IDENTIFIER' PRINT '' PRINT '/* Query Execution Statements */' PRINT 'SET ARITHABORT' PRINT 'SET ARITHIGNORE' PRINT 'SET FMTONLY' PRINT 'SET NOCOUNT' PRINT 'SET NOEXEC' PRINT 'SET NUMERIC_ROUNDABORT' PRINT 'SET PARSEONLY' PRINT 'SET QUERY_GOVERNOR_COST_LIMIT' PRINT 'SET ROWCOUNT' PRINT 'SET TEXTSIZE' PRINT '' PRINT '/* ISO Settings Statements */' PRINT 'SET ANSI_DEFAULTS' PRINT 'SET ANSI_NULL_DFLT_OFF' PRINT 'SET ANSI_NULL_DFLT_ON' PRINT 'SET ANSI_NULLS' PRINT 'SET ANSI_PADDING' PRINT 'SET ANSI_WARNINGS' PRINT '' PRINT '/* Statistics Statements */' PRINT 'SET FORCEPLAN' PRINT 'SET SHOWPLAN_ALL' PRINT 'SET SHOWPLAN_TEXT' PRINT 'SET SHOWPLAN_XML' PRINT 'SET STATISTICS IO' PRINT 'SET STATISTICS XML' PRINT 'SET STATISTICS PROFILE' PRINT 'SET STATISTICS TIME' PRINT '' PRINT '/* Transactions Statements */' PRINT 'SET IMPLICIT_TRANSACTIONS' PRINT 'SET REMOTE_PROC_TRANSACTIONS' PRINT 'SET TRANSACTION ISOLATION LEVEL' PRINT 'SET XACT_ABORT' PRINT '' PRINT '/* Informational Statements */' PRINT 'DBCC INPUTBUFFER' PRINT 'DBCC SHOWCONTIG' PRINT 'DBCC OPENTRAN' PRINT 'DBCC SQLPERF' PRINT 'DBCC OUTPUTBUFFER' PRINT 'DBCC TRACESTATUS' PRINT 'DBCC PROCCACHE' PRINT 'DBCC USEROPTIONS' PRINT 'DBCC SHOW_STATISTICS' PRINT 'DBCC MEMORYSTATUS' PRINT '' PRINT '/* Validation Statements */' PRINT 'DBCC CHECKALLOC' PRINT 'DBCC CHECKFILEGROUP' PRINT 'DBCC CHECKCATALOG' PRINT 'DBCC CHECKIDENT' PRINT 'DBCC CHECKCONSTRAINTS' PRINT 'DBCC CHECKTABLE' PRINT 'DBCC CHECKDB' PRINT '' PRINT '/* Maintenance Statements */' PRINT 'DBCC CLEANTABLE' PRINT 'DBCC INDEXDEFRAG' PRINT 'DBCC DBREINDEX' PRINT 'DBCC SHRINKDATABASE' PRINT 'DBCC DROPCLEANBUFFERS' PRINT 'DBCC SHRINKFILE' PRINT 'DBCC FREEPROCCACHE' PRINT 'DBCC UPDATEUSAGE' PRINT '' PRINT '/* Miscellaneous Statements */' PRINT 'DBCC dllname (FREE)' PRINT 'DBCC HELP' PRINT 'DBCC FLUSHAUTHCACHE' PRINT 'DBCC TRACEOFF' PRINT 'DBCC FREESESSIONCACHE' PRINT 'DBCC TRACEON' PRINT 'DBCC FREESYSTEMCACHE' PRINT 'DBCC CLONEDATABASE' RETURN END DECLARE @SCRIPT NVARCHAR(MAX) IF @OBJECT LIKE '%$%' BEGIN SET @SCRIPT = N' /* Retrive Procedure Definition */ ' IF @OBJECT LIKE '%$$%' BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [?]; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.OBJECTS A INNER JOIN SYS.SQL_MODULES B ON A.OBJECT_ID = B.OBJECT_ID WHERE B.DEFINITION LIKE ''' + REPLACE(@OBJECT, '$', '%') + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Catalog View] = ''sys.sql_modules'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Database] = DB_NAME() ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.SQL_MODULES B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE B.DEFINITION LIKE ''' + REPLACE(@OBJECT, '$', '%') + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2, 3, 4 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN IF @OBJECT LIKE '%$$%' BEGIN EXEC sp_MSforeachdb @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END RETURN END IF @OBJECT LIKE '%#%' BEGIN SET @SCRIPT = N' /* Search for Objects */ ' IF @OBJECT LIKE '%##%' BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [?]; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Catalog View] = ''sys.objects'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Database] = DB_NAME() ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Description] = ''['' + B.NAME + ''] '' + CAST(B.VALUE AS NVARCHAR(4000)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B ON A.OBJECT_ID = B.MAJOR_ID AND B.MINOR_ID = 0 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2, 3, 4 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN IF @OBJECT LIKE '%##%' BEGIN EXEC sp_MSforeachdb @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = N' /* Search for Columns */ ' IF @OBJECT LIKE '%##%' BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [?]; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.COLUMNS WHERE NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Catalog View] = ''sys.columns'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Database] = DB_NAME() ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Collation = B.collation_name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Description] = ''['' + C.NAME + ''] '' + CAST(C.VALUE AS NVARCHAR(4000)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES C ON B.OBJECT_ID = C.MAJOR_ID AND B.COLUMN_ID = C.MINOR_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE B.NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2, 3, 4, 5 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN IF @OBJECT LIKE '%##%' BEGIN EXEC sp_MSforeachdb @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = N' /* Search for Indexes */ ' IF @OBJECT LIKE '%##%' BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [?]; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.INDEXES WHERE NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Catalog View] = ''sys.indexes'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Database] = DB_NAME() ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Index] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE B.NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2, 3, 4, 5 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN IF @OBJECT LIKE '%##%' BEGIN EXEC sp_MSforeachdb @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = N' /* Search for Types */ ' IF @OBJECT LIKE '%##%' BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [?]; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.TYPES WHERE NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Catalog View] = ''sys.types'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Database] = DB_NAME() ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.TYPES A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2, 3, 4 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN IF @OBJECT LIKE '%##%' BEGIN EXEC sp_MSforeachdb @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = N' /* Search for Table Types */ ' IF @OBJECT LIKE '%##%' BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [?]; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.TYPES WHERE NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Catalog View] = ''sys.table_types'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Database] = DB_NAME() ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.TABLE_TYPES A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE NAME LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2, 3, 4 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN IF @OBJECT LIKE '%##%' BEGIN EXEC sp_MSforeachdb @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = N' /* Search for EXTENDED_PROPERTIES */ ' IF @OBJECT LIKE '%##%' BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [?]; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.EXTENDED_PROPERTIES WHERE CLASS IN (1, 2, 7) AND (CAST(NAME AS NVARCHAR(4000)) LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''' OR CAST(VALUE AS NVARCHAR(4000)) LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''')) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' [Catalog View] = ''sys.extended_properties'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Class] = A.CLASS_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Database] = DB_NAME() ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(B.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Object Type] = B.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Major] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Minor] = COALESCE(C.NAME, D.NAME) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Description] = A.VALUE ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.EXTENDED_PROPERTIES A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS B ON A.MAJOR_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND A.MINOR_ID = C.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.INDEXES D ON B.OBJECT_ID = D.OBJECT_ID AND A.MINOR_ID = D.INDEX_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.CLASS IN (1, 2, 7) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND (CAST(A.NAME AS NVARCHAR(4000)) LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' OR CAST(A.VALUE AS NVARCHAR(4000)) LIKE ''' + REPLACE(@OBJECT, '#', '%') + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2, 3, 4, 5, 6, 7, 8 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN IF @OBJECT LIKE '%##%' BEGIN EXEC sp_MSforeachdb @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END RETURN END DECLARE @ProductVersion INT DECLARE @DATABASE SYSNAME DECLARE @SCHEMA SYSNAME SET @ProductVersion = CONVERT(INT, REPLACE(LEFT(CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion')), 2), '.', '')) SET @DATABASE = ISNULL(PARSENAME(@OBJECT, 3), DB_NAME()) SET @SCHEMA = PARSENAME(@OBJECT, 2) SET @OBJECT = PARSENAME(@OBJECT, 1) DECLARE @HELPTEXT SYSNAME SET @HELPTEXT = (SELECT TOP 1 NAME + ' ' FROM master.SYS.OBJECTS WHERE NAME = 'SP_HELPTEXTX' AND IS_MS_SHIPPED = 1) IF @HELPTEXT IS NULL SET @HELPTEXT = (SELECT TOP 1 NAME + ' ' FROM master.SYS.OBJECTS WHERE NAME LIKE 'SP_HELPTEXT%' AND IS_MS_SHIPPED = 1) IF @HELPTEXT IS NULL SET @HELPTEXT = 'SP_HELPTEXT' SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT @TYPE = RTRIM(LTRIM(A.TYPE)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , @TYPE_DESC = A.TYPE_DESC ' IF @SCHEMA IS NULL BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , @SCHEMA = B.NAME ' END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.SCHEMAS B ON A.SCHEMA_ID = B.SCHEMA_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' IF @SCHEMA IS NOT NULL BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND B.NAME = ''' + @SCHEMA + ''' ' END DECLARE @TYPE VARCHAR(2), @TYPE_DESC NVARCHAR(60) IF @SCHEMA IS NULL BEGIN EXEC SP_EXECUTESQL @SCRIPT, N'@TYPE VARCHAR(2) OUTPUT, @TYPE_DESC NVARCHAR(60) OUTPUT, @SCHEMA SYSNAME OUTPUT', @TYPE = @TYPE OUTPUT, @TYPE_DESC = @TYPE_DESC OUTPUT, @SCHEMA = @SCHEMA OUTPUT END ELSE BEGIN EXEC SP_EXECUTESQL @SCRIPT, N'@TYPE VARCHAR(2) OUTPUT, @TYPE_DESC NVARCHAR(60) OUTPUT', @TYPE = @TYPE OUTPUT, @TYPE_DESC = @TYPE_DESC OUTPUT END IF @TYPE = 'C' BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(B.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Table] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Definition] = CAST(A.DEFINITION AS NVARCHAR(4000)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Description] = ''['' + D.NAME + ''] '' + ISNULL(CAST(D.VALUE AS NVARCHAR(4000)), '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.CHECK_CONSTRAINTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS B ON A.PARENT_OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND A.PARENT_COLUMN_ID = C.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES D ON A.OBJECT_ID = D.MAJOR_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND B.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 2, 3, 4, 5' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END ELSE IF @TYPE IN ('P', 'FN', 'TR', 'IF') BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' EXEC ' + @HELPTEXT + ' ''[' + @SCHEMA + '].[' + @OBJECT + ']'' ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END ELSE IF @TYPE = 'IT' BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' EXEC SP_HELP ''[' + @SCHEMA + '].[' + @OBJECT + ']'' ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END ELSE IF @TYPE = 'TF' BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' EXEC ' + @HELPTEXT + ' ''[' + @SCHEMA + '].[' + @OBJECT + ']'' ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END SET @SCRIPT = N' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [User Type] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [System Type] = D.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Max Length] = B.MAX_LENGTH ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Precision ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Scale ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Nullable = CASE WHEN B.IS_NULLABLE = ''0'' THEN ''NOT NULL'' ELSE '''' END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES C ON B.USER_TYPE_ID = C.USER_TYPE_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES D ON B.SYSTEM_TYPE_ID = D.SYSTEM_TYPE_ID AND B.SYSTEM_TYPE_ID = D.USER_TYPE_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND A.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 2, 3, 4 ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END ELSE IF @TYPE = 'D' BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = C.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Table] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Definition] = CAST(ISNULL(D.DEFINITION, E.DEFINITION) AS NVARCHAR(4000)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS C ON B.DEFAULT_OBJECT_ID = C.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.DEFAULT_CONSTRAINTS D ON B.DEFAULT_OBJECT_ID = D.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SQL_MODULES E ON B.DEFAULT_OBJECT_ID = E.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE C.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND C.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 2, 3, 4, 5 ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END ELSE IF @TYPE = 'R' BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = C.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Table] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Definition] = CAST(D.DEFINITION AS NVARCHAR(4000)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS C ON B.RULE_OBJECT_ID = C.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.SQL_MODULES D ON C.OBJECT_ID = D.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE C.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND C.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 2, 3, 4, 5 ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END ELSE IF @TYPE = 'F' BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(''tempdb..#Foreign'', N''U'') IS NOT NULL DROP TABLE #Foreign ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = B.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(C.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Table] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = D.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Schema] = SCHEMA_NAME(E.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Table] = E.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Column] = F.NAME INTO #Foreign ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.FOREIGN_KEY_COLUMNS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS B ON A.CONSTRAINT_OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS C ON A.PARENT_OBJECT_ID = C.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND A.PARENT_COLUMN_ID = D.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS E ON A.REFERENCED_OBJECT_ID = E.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS F ON E.OBJECT_ID = F.OBJECT_ID AND A.REFERENCED_COLUMN_ID = F.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE B.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND B.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' [Type] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Table] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = STUFF((SELECT '', '' + [Column] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Foreign B ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.Name = B.Name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')), 1, 2, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Schema] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Table] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Column] = STUFF((SELECT '', '' + [Reference Column] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Foreign B ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.Name = B.Name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')), 1, 2, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Foreign A ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END ELSE IF @TYPE IN ('U', 'V') BEGIN DECLARE @SCRIPT1 NVARCHAR(MAX); SET @SCRIPT1 = '' DECLARE @SCRIPT2 NVARCHAR(MAX); SET @SCRIPT2 = '' DECLARE @SCRIPT3 NVARCHAR(MAX); SET @SCRIPT3 = '' IF @TYPE = 'V' BEGIN SET @SCRIPT = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' EXEC ' + @HELPTEXT + ' ''[' + @SCHEMA + '].[' + @OBJECT + ']'' ' IF @DEBUG = 1 BEGIN PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT1 = N' /* TYPE : ' + @TYPE + ', ' + @TYPE_DESC + ' */ ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' USE [' + @DATABASE + ']; ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT * FROM SYS.EXTENDED_PROPERTIES WHERE MAJOR_ID = OBJECT_ID(''' + @OBJECT + ''') AND MINOR_ID = 0) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' SELECT [Type] = A.TYPE_DESC ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , A.Name ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Property = B.Name ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , B.Value ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.EXTENDED_PROPERTIES B ON A.OBJECT_ID = B.MAJOR_ID AND B.MINOR_ID = 0 ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(''tempdb..#Table'', N''U'') IS NOT NULL DROP TABLE #Table ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' SELECT [Type] = A.TYPE_DESC ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , A.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Column] = B.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Description] = ''['' + L.NAME + ''] '' + CAST(L.VALUE AS NVARCHAR(4000)) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [User Type] = C.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [System Type] = D.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , B.MAX_LENGTH ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , B.Precision ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , B.Scale ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Identity] = ISNULL(''('' + CAST(E.SEED_VALUE AS VARCHAR(10)) + '','' + CAST(E.INCREMENT_VALUE AS VARCHAR(10)) + '')'', '''') ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Nullable = CASE WHEN B.IS_NULLABLE = ''0'' THEN ''NOT NULL'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Collation = B.collation_name ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Computed] = ISNULL(CAST(F.Definition AS NVARCHAR(4000)), '''') ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Persisted] = CASE WHEN F.IS_PERSISTED = 1 THEN ''Y'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Default] = ISNULL(ISNULL(G.NAME + '' AS '' + CAST(G.Definition AS NVARCHAR(4000)), CAST(H.Definition AS NVARCHAR(4000))), '''') ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Check] = ISNULL(J.Name + '' AS '' + CAST(J.Definition AS NVARCHAR(4000)), '''') ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Rule] = ISNULL(CAST(K.Definition AS NVARCHAR(4000)), '''') ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , B.COLUMN_ID INTO #Table ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES C ON B.USER_TYPE_ID = C.USER_TYPE_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES D ON B.SYSTEM_TYPE_ID = D.SYSTEM_TYPE_ID AND B.SYSTEM_TYPE_ID = D.USER_TYPE_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.IDENTITY_COLUMNS E ON B.OBJECT_ID = E.OBJECT_ID AND B.COLUMN_ID = E.COLUMN_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.COMPUTED_COLUMNS F ON B.OBJECT_ID = F.OBJECT_ID AND B.COLUMN_ID = F.COLUMN_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.DEFAULT_CONSTRAINTS G ON B.DEFAULT_OBJECT_ID = G.OBJECT_ID AND B.COLUMN_ID = G.PARENT_COLUMN_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SQL_MODULES H ON B.DEFAULT_OBJECT_ID = H.OBJECT_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.CHECK_CONSTRAINTS J ON B.OBJECT_ID = J.PARENT_OBJECT_ID AND B.COLUMN_ID = J.PARENT_COLUMN_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SQL_MODULES K ON B.RULE_OBJECT_ID = K.OBJECT_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES L ON B.OBJECT_ID = L.MAJOR_ID AND B.COLUMN_ID = L.MINOR_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' AND A.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''') ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' SELECT [Type] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Schema] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Name] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Column] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Description] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [User Type] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [System Type] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Max Length] = MAX_LENGTH ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Precision ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Scale ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Identity] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Nullable ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Collation ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Computed] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Persisted] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Default] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Check] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Rule] ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' FROM #Table ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' ORDER BY COLUMN_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT * FROM SYS.CHECK_CONSTRAINTS A INNER JOIN SYS.OBJECTS B ON A.PARENT_OBJECT_ID = B.OBJECT_ID WHERE A.PARENT_COLUMN_ID = 0 AND B.NAME = ''' + @OBJECT + ''' AND B.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''')) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' SELECT [Check Constraint Name] = A.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Definition] = A.DEFINITION ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' FROM SYS.CHECK_CONSTRAINTS A ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS B ON A.PARENT_OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' WHERE A.PARENT_COLUMN_ID = 0 ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' AND B.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' AND B.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''') ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' END ' IF @TYPE = 'U' BEGIN SET @SCRIPT2 = ' DECLARE @COL1 NVARCHAR(MAX); ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @COL1 = N'''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SELECT @COL1 = @COL1 + '' , '' + [Column] + '' '' + ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' CASE WHEN [COMPUTED] <> '''' THEN ''AS '' + [COMPUTED] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ELSE ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' UPPER([System Type]) + ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' CASE WHEN [System Type] IN (''numeric'', ''decimal'') THEN ''('' + CONVERT(VARCHAR(3), PRECISION) + '','' + CONVERT(VARCHAR(3), SCALE) + '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHEN [System Type] IN (''char'', ''varchar'', ''binary'', ''varbinary'') THEN ''('' + REPLACE(CONVERT(VARCHAR(5), MAX_LENGTH), ''-1'', ''MAX'') + '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHEN [System Type] IN (''nchar'', ''nvarchar'') THEN ''('' + CASE WHEN MAX_LENGTH = -1 THEN ''MAX'' ELSE CONVERT(VARCHAR(5), MAX_LENGTH / 2) END + '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ELSE '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' END + ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' CASE WHEN [IDENTITY] <> '''' THEN '' IDENTITY'' + [IDENTITY] ELSE '''' END + ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' CASE WHEN NULLABLE = '''' THEN '' '' ELSE '' NOT NULL'' END ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' END + CHAR(13) + CHAR(10) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Table ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''CREATE TABLE [' + @SCHEMA + '].[' + @OBJECT + ']'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''('' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT STUFF(@COL1, 5, 2, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''GO'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' DECLARE @SP VARCHAR(MAX) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' DECLARE @VAR VARCHAR(MAX) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' DECLARE @COL2 VARCHAR(MAX) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' DECLARE @COL3 VARCHAR(MAX) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @SP = '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @VAR = '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @COL2 = '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @COL3 = '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SELECT @VAR = @VAR + '' , @'' + [Column] + '' '' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' + UPPER([System Type]) + CASE WHEN [System Type] IN (''numeric'', ''decimal'') THEN ''('' + CONVERT(VARCHAR(3), PRECISION) + '','' + CONVERT(VARCHAR(3), SCALE) + '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHEN [System Type] IN (''char'', ''varchar'', ''binary'', ''varbinary'') THEN ''('' + REPLACE(CONVERT(VARCHAR(5), MAX_LENGTH), ''-1'', ''MAX'') + '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHEN [System Type] IN (''nchar'', ''nvarchar'') THEN ''('' + CASE WHEN MAX_LENGTH = -1 THEN ''MAX'' ELSE CONVERT(VARCHAR(5), MAX_LENGTH / 2) END + '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ELSE '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' END + CHAR(13) + CHAR(10) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , @COL2 = @COL2 + '' , '' + [Column] + CHAR(13) + CHAR(10) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , @COL3 = @COL3 + '' , '' + [Column] + '' = @'' + [Column] + CHAR(13) + CHAR(10) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Table ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''CREATE PROCEDURE USP_T_'' + ''' + @OBJECT + ''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT STUFF(@VAR, 5, 2, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''AS'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''SELECT '' + STUFF(@COL2, 1, 6, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''FROM '' + ''' + @OBJECT + ''' + '' WITH(NOLOCK, READUNCOMMITTED)'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''WHERE '' + REPLACE(STUFF(@COL3, 1, 6, ''''), '','', ''AND'') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''INSERT INTO '' + ''' + @OBJECT + ''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''('' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT STUFF(@COL2, 5, 2, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''VALUES'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''('' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT STUFF(REPLACE(@COL2, '', '', '', @''), 5, 2, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT '')'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''UPDATE '' + ''' + @OBJECT + ''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''SET '' + STUFF(@COL3, 1, 6, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' PRINT ''WHERE '' + REPLACE(STUFF(@COL3, 1, 6, ''''), '','', ''AND'') ' END IF @DEBUG = 1 BEGIN PRINT @SCRIPT1 PRINT '' PRINT @SCRIPT2 END ELSE BEGIN SET @SCRIPT = @SCRIPT1 + CHAR(13) + CHAR(10) + @SCRIPT2 EXEC (@SCRIPT) END DECLARE @COMPATIBILITY_LEVEL TINYINT SET @COMPATIBILITY_LEVEL = (SELECT COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE NAME = @DATABASE) DECLARE @SORTKEY NVARCHAR(MAX) SET @SCRIPT1 = N' USE [' + @DATABASE + ']; ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(N''tempdb..#Index'', N''U'') IS NOT NULL DROP TABLE #Index ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' A.OBJECT_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , B.INDEX_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Index Name] = ISNULL(B.NAME, B.TYPE_DESC) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Key Columns] = D.NAME + CASE WHEN C.IS_INCLUDED_COLUMN = 1 THEN NULL WHEN C.IS_INCLUDED_COLUMN = 0 AND C.IS_DESCENDING_KEY = 1 THEN '' DESC'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Included Columns] = CASE WHEN C.IS_INCLUDED_COLUMN = 1 THEN D.NAME ELSE NULL END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Filter Definition] = ' + CASE WHEN @COMPATIBILITY_LEVEL >= 100 THEN 'CAST(B.FILTER_DEFINITION AS NVARCHAR(4000))' ELSE 'NULL' END SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , ROWS = REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, F.ROWS), 112), ''.00'', SPACE(0)) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [Description] = CASE B.TYPE WHEN 0 THEN ''Heap'' WHEN 1 THEN ''Clustered'' ELSE ''Nonclustered'' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' + CASE WHEN B.IGNORE_DUP_KEY <> 0 THEN '', Ignore Duplicate Keys'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' + CASE WHEN B.IS_UNIQUE <> 0 THEN '', Unique'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' + CASE WHEN B.IS_HYPOTHETICAL <> 0 THEN '', Hypothetical'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' + CASE WHEN B.IS_PRIMARY_KEY <> 0 THEN '', Primary Key'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' + CASE WHEN B.IS_UNIQUE_CONSTRAINT <> 0 THEN '', Unique Key'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , [File Group] = E.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Disabled = CASE WHEN B.IS_DISABLED = 1 THEN ''Disabled'' ELSE '''' END ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Updated = STATS_DATE(A.OBJECT_ID, B.INDEX_ID) ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , Ordinal = C.KEY_ORDINAL ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , B.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , C.IS_INCLUDED_COLUMN ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , C.INDEX_COLUMN_ID INTO #Index ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.DATA_SPACES E ON B.DATA_SPACE_ID = E.DATA_SPACE_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.PARTITIONS F ON B.OBJECT_ID = F.OBJECT_ID AND B.INDEX_ID = F.INDEX_ID ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' ORDER BY B.NAME ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , C.IS_INCLUDED_COLUMN ' SET @SCRIPT1 = @SCRIPT1 + CHAR(13) + CHAR(10) + ' , C.INDEX_COLUMN_ID ' SET @SCRIPT2 = ' INSERT INTO #Index ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SELECT A.OBJECT_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , B.STATS_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Index Name] = B.NAME ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Key Columns] = D.NAME ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Included Columns] = NULL ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Filter Definition] = ' + CASE WHEN @COMPATIBILITY_LEVEL >= 100 THEN 'CAST(B.FILTER_DEFINITION AS NVARCHAR(4000))' ELSE 'NULL' END SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , ROWS = NULL ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Description] = ''Statistics'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [File Group] = '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Disabled = '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Updated = STATS_DATE(A.OBJECT_ID, B.STATS_ID) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Ordinal = C.STATS_COLUMN_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , '''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.STATS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.STATS_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.STATS_ID = C.STATS_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.INDEXES E ON B.OBJECT_ID = E.OBJECT_ID AND B.STATS_ID = E.INDEX_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' AND E.NAME IS NULL ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' DECLARE @INDEX_ID INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @INDEX_ID = (SELECT TOP 1 INDEX_ID FROM #INDEX WHERE [Description] LIKE ''Clustered%'' AND Disabled = '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' IF @INDEX_ID IS NULL SET @INDEX_ID = (SELECT TOP 1 INDEX_ID FROM #INDEX WHERE [Description] LIKE ''%Primary Key%'' AND Disabled = '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' IF @INDEX_ID IS NULL SET @INDEX_ID = (SELECT TOP 1 INDEX_ID FROM #INDEX WHERE [Description] LIKE ''%Unique%'' AND Disabled = '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' IF @INDEX_ID IS NULL SET @INDEX_ID = (SELECT MIN(INDEX_ID) INDEX_ID FROM #INDEX WHERE Disabled = '''' AND [Key Columns] > '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' IF @INDEX_ID IS NOT NULL ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @SORTKEY = N'''' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SELECT @SORTKEY = @SORTKEY + [Key Columns] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM (SELECT DISTINCT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' INDEX_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , ISNULL('', '' + REPLACE([Key Columns] + '' DESC'', ''DESC DESC'', ''ASC''), '''') AS [Key Columns] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Ordinal ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Index) AS [Index] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHERE INDEX_ID = @INDEX_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ORDER BY Ordinal ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' END ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SET @SORTKEY = SUBSTRING(@SORTKEY, 3, 8000) ' IF @COMPATIBILITY_LEVEL >= 90 BEGIN IF @OPTION = 'INDEXSIZE' BEGIN SET @SCRIPT3 = N' IF EXISTS (SELECT 1 FROM #Index) ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' [Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Key Columns] = STUFF(CAST((SELECT ISNULL('', '' + [Key Columns], '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FROM (SELECT DISTINCT [Index Name], [Key Columns], Ordinal FROM #Index) Z ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' ORDER BY Z.Ordinal ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Included Columns] = ISNULL(STUFF(CAST((SELECT ISNULL('', '' + [Included Columns], '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FROM (SELECT DISTINCT [Index Name], [Included Columns], Ordinal FROM #Index) Z ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' ORDER BY Z.Ordinal ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, ''''), '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Filter Definition] = ISNULL([Filter Definition], '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Row Counts] = A.ROWS ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Page Counts] = REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, CONVERT(NUMERIC(38, 2), B.PAGE_COUNT)), 112), ''.00'', SPACE(0)) ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Index Size(MB)] = REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, CONVERT(NUMERIC(38, 2), B.PAGE_COUNT * 8.0 / 1024)), 112), ''.00'', SPACE(0)) ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Avg Fragmentation] = CONVERT(NUMERIC(5, 2), B.AVG_FRAGMENTATION_IN_PERCENT) ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Alloc Unit Type] = B.ALLOC_UNIT_TYPE_DESC ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Description] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [File Group] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , Disabled ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , Updated ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FROM #Index A ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), OBJECT_ID(''' + @OBJECT + '''), NULL, NULL, ''LIMITED'') B ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' ORDER BY [Description], [Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' END ' END ELSE BEGIN SET @SCRIPT3 = N' IF EXISTS (SELECT 1 FROM #Index) ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' [Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Key Columns] = STUFF(CAST((SELECT ISNULL('', '' + [Key Columns], '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FROM (SELECT DISTINCT [Index Name], [Key Columns], Ordinal FROM #Index) Z ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' ORDER BY Ordinal ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Included Columns] = ISNULL(STUFF(CAST((SELECT ISNULL('', '' + [Included Columns], '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FROM (SELECT DISTINCT [Index Name], [Included Columns], Ordinal FROM #Index) Z ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' ORDER BY Ordinal ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, ''''), '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Filter Definition] = ISNULL([Filter Definition], '''') ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Row Counts] = A.ROWS ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Description] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [File Group] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , Disabled ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , Updated ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FROM #Index A ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' ORDER BY [Description], [Index Name] ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' END ' END SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' SELECT [Index Name] = ISNULL(B.NAME, B.TYPE_DESC) ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Partition Number] = C.PARTITION_NUMBER ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [Allocation Type] = D.TYPE_DESC ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' , [File Group] = E.NAME ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.PARTITIONS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.ALLOCATION_UNITS D ON C.PARTITION_ID = D.CONTAINER_ID ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.DATA_SPACES E ON D.DATA_SPACE_ID = E.DATA_SPACE_ID ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT3 = @SCRIPT3 + CHAR(13) + CHAR(10) + ' ORDER BY 1, 2 ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT1 PRINT '' PRINT @SCRIPT2 PRINT '' PRINT @SCRIPT3 END ELSE BEGIN SET @SCRIPT = @SCRIPT1 + CHAR(13) + CHAR(10) + @SCRIPT2 + CHAR(13) + CHAR(10) + @SCRIPT3 EXEC SP_EXECUTESQL @SCRIPT, N'@SORTKEY NVARCHAR(4000) OUTPUT', @SORTKEY = @SORTKEY OUTPUT END END ELSE BEGIN IF @OPTION = 'INDEXSIZE' BEGIN SET @SCRIPT2 = N' DECLARE @FragList TABLE ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ( ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ObjectName CHAR(255) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Object_ID INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , IndexName CHAR(255) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Index_ID INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Level INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Pages INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Rows INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , MinRecSize INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , MaxRecSize INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , AvgRecSize FLOAT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , ForRecCount INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Extents INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , ExtentSwitches INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , AvgFreeBytes FLOAT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , AvgPageDensity FLOAT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , ScanDensity FLOAT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , BestCount INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , ActualCount INT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , LogicalFrag FLOAT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , ExtentFrag FLOAT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' IF @TYPE = ''U'' ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' INSERT @FragList ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' EXEC (''DBCC SHOWCONTIG('''''' + ''' + @OBJECT + ''' + '''''', '''''''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST'') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' END ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM #Index) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' [Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Key Columns] = STUFF(CAST((SELECT ISNULL('', '' + [Key Columns], '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Index Z ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ORDER BY Ordinal ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Included Columns] = ISNULL(STUFF(CAST((SELECT ISNULL('', '' + [Included Columns], '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Index Z ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ORDER BY Ordinal ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, ''''), '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Filter Definition] = ISNULL([Filter Definition], '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Row Counts] = A.ROWS ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Index Size(MB)] = REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, CONVERT(NUMERIC(38, 2), B.Pages * 8.0 / 1024)), 112), ''.00'', SPACE(0)) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Avg Fragmentation] = CONVERT(NUMERIC(5, 2), B.LogicalFrag) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Description] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [File Group] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Disabled ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Updated ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Index A ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN @FragList B ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ORDER BY [Description], [Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' END ' END ELSE BEGIN SET @SCRIPT2 = N' IF EXISTS (SELECT 1 FROM #Index) ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' [Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Key Columns] = STUFF(CAST((SELECT ISNULL('', '' + [Key Columns], '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Index Z ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ORDER BY Ordinal ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Included Columns] = ISNULL(STUFF(CAST((SELECT ISNULL('', '' + [Included Columns], '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Index Z ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' WHERE A.[Index Name] = Z.[Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ORDER BY Ordinal ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')) AS NVARCHAR(4000)), 1, 2, ''''), '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Filter Definition] = ISNULL([Filter Definition], '''') ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Row Counts] = A.ROWS ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [Description] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , [File Group] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Disabled ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' , Updated ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' FROM #Index A ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' ORDER BY [Description], [Index Name] ' SET @SCRIPT2 = @SCRIPT2 + CHAR(13) + CHAR(10) + ' END ' END IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT1 PRINT '' PRINT @SCRIPT2 END ELSE BEGIN SET @SCRIPT = @SCRIPT1 + CHAR(13) + CHAR(10) + @SCRIPT2 EXEC SP_EXECUTESQL @SCRIPT, N'@SORTKEY NVARCHAR(4000) OUTPUT', @SORTKEY = @SORTKEY OUTPUT END END SET @SCRIPT = N' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(''tempdb..#Foreign'', N''U'') IS NOT NULL DROP TABLE #Foreign ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = B.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(C.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Table] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = D.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Schema] = SCHEMA_NAME(E.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Table] = E.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Column] = F.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Level] = CASE WHEN E.NAME = ''' + @OBJECT + ''' THEN 0 ELSE 1 END INTO #Foreign ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.FOREIGN_KEY_COLUMNS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS B ON A.CONSTRAINT_OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS C ON A.PARENT_OBJECT_ID = C.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND A.PARENT_COLUMN_ID = D.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS E ON A.REFERENCED_OBJECT_ID = E.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS F ON E.OBJECT_ID = F.OBJECT_ID AND A.REFERENCED_COLUMN_ID = F.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE (C.NAME = ''' + @OBJECT + ''' AND C.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMA + ''')) OR E.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM #Foreign) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT Z.[Type] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Z.[Name] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Z.[Schema] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Z.[Table] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = CAST(Z.[Column] AS NVARCHAR(4000)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Z.[Reference Schema] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Z.[Reference Table] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Column] = CAST(Z.[Reference Column] AS NVARCHAR(4000)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM (SELECT DISTINCT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' [Type] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Table] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = STUFF((SELECT '', '' + [Column] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Foreign B ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.[Name] = B.[Name] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')), 1, 2, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Schema] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Table] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Reference Column] = STUFF((SELECT '', '' + [Reference Column] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Foreign B ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.[Name] = B.[Name] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')), 1, 2, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Level] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Foreign A) Z ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY Z.[Level] DESC, 1, 2, 3, 4, 5 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END SET @SCRIPT = N' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(''tempdb..#Trigger'', N''U'') IS NOT NULL DROP TABLE #Trigger ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE PARENT_OBJECT_ID = OBJECT_ID(''' + @OBJECT + ''') AND TYPE = ''TR'') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' BEGIN ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = B.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Events] = D.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Disabled] = CASE WHEN C.IS_DISABLED = 1 THEN ''Y'' ELSE '''' END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Instead of Trigger] = CASE WHEN C.IS_INSTEAD_OF_TRIGGER = 1 THEN ''Y'' ELSE '''' END INTO #Trigger ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS B ON A.OBJECT_ID = B.PARENT_OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TRIGGERS C ON B.OBJECT_ID = C.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TRIGGER_EVENTS D ON B.OBJECT_ID = D.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND B.TYPE = ''TR'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT DISTINCT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' [Type] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Events] = STUFF((SELECT '', '' + [Events] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Trigger B ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.[Name] = B.[Name] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FOR XML PATH('''')), 1, 2, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Disabled] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Instead of Trigger] ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #Trigger A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 1 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' END ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END SET @SCRIPT = N' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT TOP 100 * ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM [' + @SCHEMA + '].[' + @OBJECT + '] WITH(NOLOCK, READUNCOMMITTED) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + CASE WHEN @SORTKEY IS NULL THEN '' ELSE ' ORDER BY ' + @SORTKEY END IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT @TYPE_DESC = ''STATS'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.STATS ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE NAME = ''' + @OBJECT + ''' ' SET @TYPE_DESC = NULL EXEC SP_EXECUTESQL @SCRIPT, N'@TYPE_DESC NVARCHAR(60) OUTPUT', @TYPE_DESC = @TYPE_DESC OUTPUT IF @TYPE_DESC = 'STATS' BEGIN SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(''tempdb..#STAT_HEADER'', N''U'') IS NOT NULL DROP TABLE #STAT_HEADER ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(''tempdb..#DENSITY_VECTOR'', N''U'') IS NOT NULL DROP TABLE #DENSITY_VECTOR ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' IF OBJECT_ID(''tempdb..#HISTOGRAM'', N''U'') IS NOT NULL DROP TABLE #HISTOGRAM ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' DECLARE @TABLE SYSNAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' DECLARE @SCHEMA SYSNAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT @TABLE = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , @SCHEMA = SCHEMA_NAME(B.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.STATS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.OBJECTS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + ''' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' DECLARE @Rows BIGINT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' CREATE TABLE #STAT_HEADER ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ( ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' Name SYSNAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Updated DATETIME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Rows BIGINT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Rows_Sampled BIGINT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Steps INT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Density REAL ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Average_Key_Length INT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , String_Index VARCHAR(MAX) ' IF @ProductVersion >= 10 BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Filter_Expression NVARCHAR(MAX) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Unfiltered_Rows BIGINT ' END IF @ProductVersion >= 13 BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Persisted_Sample_Percent REAL ' END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INSERT #STAT_HEADER (Name, Updated, Rows, Rows_Sampled, Steps, Density, Average_Key_Length, String_Index' IF @ProductVersion >= 10 BEGIN SET @SCRIPT = @SCRIPT + ', Filter_Expression, Unfiltered_Rows' END IF @ProductVersion >= 13 BEGIN SET @SCRIPT = @SCRIPT + ', Persisted_Sample_Percent' END SET @SCRIPT = @SCRIPT + ') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' EXEC (''DBCC SHOW_STATISTICS ('''''' + @SCHEMA + ''.'' + @TABLE + '''''', ''''' + @OBJECT + ''''') WITH NO_INFOMSGS, STAT_HEADER'') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT Name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Updated ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Rows = REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, Rows), 112), ''.00'', SPACE(0)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Rows Sampled] = REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, Rows_Sampled), 112), ''.00'', SPACE(0)) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Sample Rate] = CONVERT(NUMERIC(5, 2), 100.0 * Rows_Sampled / Rows) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Steps ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Density ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Average Key Length] = Average_Key_Length ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [String Index] = String_Index ' IF @ProductVersion >= 10 BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Filter Expression] = Filter_Expression ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Unfiltered Rows] = REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, Unfiltered_Rows), 112), ''.00'', SPACE(0)) ' END IF @ProductVersion >= 13 BEGIN SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Persisted Sample Percent] = Persisted_Sample_Percent ' END SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #STAT_HEADER ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SET @Rows = (SELECT Rows FROM #STAT_HEADER) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' CREATE TABLE #DENSITY_VECTOR ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ( ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' All_Density REAL ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Average_Length INT ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Columns NVARCHAR(MAX) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INSERT #DENSITY_VECTOR ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' EXEC (''DBCC SHOW_STATISTICS ('''''' + @SCHEMA + ''.'' + @TABLE + '''''', ''''' + @OBJECT + ''''') WITH NO_INFOMSGS, DENSITY_VECTOR'') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [All Density] = All_Density ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Estimate Rows] = REPLACE(CONVERT(VARCHAR(32), CONVERT(MONEY, @Rows * All_Density), 1), ''.00'', '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Average Length] = Average_Length ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Columns ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #DENSITY_VECTOR ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' CREATE TABLE #HISTOGRAM ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ( ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' Range_Hi_Key NVARCHAR(1000) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Range_Rows NUMERIC(38, 2) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , EQ_Rows NUMERIC(38, 2) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Distinct_Range_Rows NUMERIC(38, 2) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Avg_Range_Rows NUMERIC(38, 2) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INSERT #HISTOGRAM ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' EXEC (''DBCC SHOW_STATISTICS ('''''' + @SCHEMA + ''.'' + @TABLE + '''''', ''''' + @OBJECT + ''''') WITH NO_INFOMSGS, HISTOGRAM'') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Range Hi Key] = Range_Hi_Key ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Range Rows] = REPLACE(CONVERT(VARCHAR(32), CONVERT(MONEY, Range_Rows), 1), ''.00'', '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [EQ Rows] = REPLACE(CONVERT(VARCHAR(32), CONVERT(MONEY, EQ_Rows), 1), ''.00'', '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Distinct Range Rows] = REPLACE(CONVERT(VARCHAR(32), CONVERT(MONEY, Distinct_Range_Rows), 1), ''.00'', '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Avg Range Rows] = REPLACE(CONVERT(VARCHAR(32), CONVERT(MONEY, Avg_Range_Rows), 1), ''.00'', '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM #HISTOGRAM ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT @TYPE_DESC = ''COLUMNS'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.COLUMNS ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE NAME = ''' + @OBJECT + ''' ' SET @TYPE_DESC = NULL EXEC SP_EXECUTESQL @SCRIPT, N'@TYPE_DESC NVARCHAR(60) OUTPUT', @TYPE_DESC = @TYPE_DESC OUTPUT IF @TYPE_DESC = 'COLUMNS' BEGIN SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Description] = ISNULL(L.VALUE, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [User Type] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [System Type] = D.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Max Length] = B.MAX_LENGTH ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Precision ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Scale ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Nullable = CASE WHEN B.IS_NULLABLE = ''0'' THEN ''NOT NULL'' ELSE '''' END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Collation = B.collation_name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Identity] = ISNULL(''('' + CONVERT(VARCHAR(10), E.SEED_VALUE) + '','' + CONVERT(VARCHAR(10), E.INCREMENT_VALUE) + '')'', '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Computed] = ISNULL(F.DEFINITION, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Default] = ISNULL(ISNULL(G.NAME + '' AS '' + G.DEFINITION, H.DEFINITION), '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Check] = ISNULL(J.Name + '' AS '' + J.DEFINITION, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Rule] = ISNULL(K.DEFINITION, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES C ON B.USER_TYPE_ID = C.USER_TYPE_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES D ON B.SYSTEM_TYPE_ID = D.SYSTEM_TYPE_ID AND B.SYSTEM_TYPE_ID = D.USER_TYPE_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.IDENTITY_COLUMNS E ON B.OBJECT_ID = E.OBJECT_ID AND B.COLUMN_ID = E.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.COMPUTED_COLUMNS F ON B.OBJECT_ID = F.OBJECT_ID AND B.COLUMN_ID = F.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.DEFAULT_CONSTRAINTS G ON B.DEFAULT_OBJECT_ID = G.OBJECT_ID AND B.COLUMN_ID = G.PARENT_COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SQL_MODULES H ON B.DEFAULT_OBJECT_ID = H.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.CHECK_CONSTRAINTS J ON B.OBJECT_ID = J.PARENT_OBJECT_ID AND B.COLUMN_ID = J.PARENT_COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SQL_MODULES K ON B.RULE_OBJECT_ID = K.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES L ON B.OBJECT_ID = L.MAJOR_ID AND B.COLUMN_ID = L.MINOR_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE B.NAME = ''' + @OBJECT + '''' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 2, 3 ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT @TYPE_DESC = ''TYPES'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.TYPES ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE NAME = ''' + @OBJECT + ''' ' SET @TYPE_DESC = NULL EXEC SP_EXECUTESQL @SCRIPT, N'@TYPE_DESC NVARCHAR(60) OUTPUT', @TYPE_DESC = @TYPE_DESC OUTPUT IF @TYPE_DESC = 'TYPES' BEGIN SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = ''Type'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , A.Name ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [System Type] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Length] = CASE WHEN C.NAME IN (N''NCHAR'', N''NVARCHAR'') AND A.MAX_LENGTH <> - 1 THEN A.MAX_LENGTH / 2 ELSE A.MAX_LENGTH END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Precision] = A.PRECISION ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Scale] = A.SCALE ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Nullable] = CASE WHEN A.IS_NULLABLE = 0 THEN ''NOT NULL'' ELSE '''' END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.TYPES AS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.SCHEMAS AS B ON B.SCHEMA_ID = A.SCHEMA_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.TYPES AS C ON (C.USER_TYPE_ID = A.SYSTEM_TYPE_ID AND C.USER_TYPE_ID = C.SYSTEM_TYPE_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' OR (C.SYSTEM_TYPE_ID = A.SYSTEM_TYPE_ID AND C.USER_TYPE_ID = A.USER_TYPE_ID AND C.IS_USER_DEFINED = 0 AND C.IS_ASSEMBLY_TYPE = 1) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + '''' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = A.TYPE_DESC ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = B.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Description] = ISNULL(L.VALUE, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [User Type] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [System Type] = D.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Max Length] = B.MAX_LENGTH ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Precision ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , B.Scale ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , Nullable = CASE WHEN B.IS_NULLABLE = ''0'' THEN ''NOT NULL'' ELSE '''' END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Identity] = ISNULL(''('' + CONVERT(VARCHAR(10), E.SEED_VALUE) + '','' + CONVERT(VARCHAR(10), E.INCREMENT_VALUE) + '')'', '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Computed] = ISNULL(F.DEFINITION, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Default] = ISNULL(ISNULL(G.NAME + '' AS '' + G.DEFINITION, H.DEFINITION), '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Check] = ISNULL(J.Name + '' AS '' + J.DEFINITION, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Rule] = ISNULL(K.DEFINITION, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.OBJECTS A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES C ON B.USER_TYPE_ID = C.USER_TYPE_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.TYPES D ON B.SYSTEM_TYPE_ID = D.SYSTEM_TYPE_ID AND B.SYSTEM_TYPE_ID = D.USER_TYPE_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.IDENTITY_COLUMNS E ON B.OBJECT_ID = E.OBJECT_ID AND B.COLUMN_ID = E.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.COMPUTED_COLUMNS F ON B.OBJECT_ID = F.OBJECT_ID AND B.COLUMN_ID = F.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.DEFAULT_CONSTRAINTS G ON B.DEFAULT_OBJECT_ID = G.OBJECT_ID AND B.COLUMN_ID = G.PARENT_COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SQL_MODULES H ON B.DEFAULT_OBJECT_ID = H.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.CHECK_CONSTRAINTS J ON B.OBJECT_ID = J.PARENT_OBJECT_ID AND B.COLUMN_ID = J.PARENT_COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SQL_MODULES K ON B.RULE_OBJECT_ID = K.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES L ON B.OBJECT_ID = L.MAJOR_ID AND B.COLUMN_ID = L.MINOR_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE C.NAME = ''' + @OBJECT + '''' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY 2, 3 ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END IF @ProductVersion >= 10 BEGIN SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT @TYPE_DESC = ''TABLE_TYPES'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.TABLE_TYPES ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE NAME = ''' + @OBJECT + ''' ' SET @TYPE_DESC = NULL EXEC SP_EXECUTESQL @SCRIPT, N'@TYPE_DESC NVARCHAR(60) OUTPUT', @TYPE_DESC = @TYPE_DESC OUTPUT IF @TYPE_DESC = 'TABLE_TYPES' BEGIN SET @SCRIPT = ' USE [' + @DATABASE + ']; ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' SELECT [Type] = ''Table Type'' ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Schema] = SCHEMA_NAME(A.SCHEMA_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Name] = A.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Column] = C.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [InPrimaryKey] = ISNULL(E.INDEX_COLUMN_ID, 0) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [IsForeignKey] = ISNULL((SELECT TOP 1 1 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.FOREIGN_KEY_COLUMNS AS COLFK ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE COLFK.PARENT_COLUMN_ID = C.COLUMN_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' AND COLFK.PARENT_OBJECT_ID = C.OBJECT_ID), 0) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [DataType] = F.NAME ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [SystemType] = ISNULL(G.NAME, '''') ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Length] = CASE WHEN G.NAME IN (N''NCHAR'', N''NVARCHAR'') AND C.MAX_LENGTH <> - 1 THEN C.MAX_LENGTH / 2 ELSE C.MAX_LENGTH END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [NumericPrecision] = C.PRECISION ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [NumericScale] = C.SCALE ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Nullable] = CASE WHEN C.IS_NULLABLE = 0 THEN ''NOT NULL'' ELSE '''' END ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' , [Computed] = C.IS_COMPUTED ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' FROM SYS.TABLE_TYPES A ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.SCHEMAS B ON A.SCHEMA_ID = B.SCHEMA_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' INNER JOIN SYS.ALL_COLUMNS C ON A.TYPE_TABLE_OBJECT_ID = C.OBJECT_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.INDEXES D ON C.OBJECT_ID = D.OBJECT_ID AND D.IS_PRIMARY_KEY = 1 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.INDEX_COLUMNS E ON D.INDEX_ID = E.INDEX_ID AND C.COLUMN_ID = E.COLUMN_ID AND C.OBJECT_ID = E.OBJECT_ID AND E.IS_INCLUDED_COLUMN = 0 ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.TYPES F ON C.USER_TYPE_ID = F.USER_TYPE_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.TYPES G ON (C.SYSTEM_TYPE_ID = G.USER_TYPE_ID AND G.SYSTEM_TYPE_ID = G.USER_TYPE_ID) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' OR (C.SYSTEM_TYPE_ID = G.SYSTEM_TYPE_ID AND C.USER_TYPE_ID = G.USER_TYPE_ID AND G.IS_USER_DEFINED = 0 AND G.IS_ASSEMBLY_TYPE = 1) ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.XML_SCHEMA_COLLECTIONS AS H ON C.XML_COLLECTION_ID = H.XML_COLLECTION_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN SYS.SCHEMAS J ON H.SCHEMA_ID = J.SCHEMA_ID ' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' WHERE A.NAME = ''' + @OBJECT + '''' SET @SCRIPT = @SCRIPT + CHAR(13) + CHAR(10) + ' ORDER BY C.COLUMN_ID ASC ' IF @DEBUG = 1 BEGIN PRINT '' PRINT @SCRIPT END ELSE BEGIN EXEC (@SCRIPT) END END END이 프로시저를 master 데이터베이스에 생성하면 모든 DB에서 동작합니다.. 아래는 실행 결과입니다.DBUpgradeHistory는 테이블 이름입니다.SP_HELPX DBUpgradeHistory이렇게 실행할 수도 있고 저는 Ctrl+F1에 SP_HELPX를 등록해두었기 때문에 이름을 선택하고 Ctrl+F1을 누르면 실행됩니다.
2014년 5월 8일 목요일
SP_HELPX
피드 구독하기:
댓글 (Atom)
댓글 없음:
댓글 쓰기