USE master
GO
IF OBJECT_ID('sp_Table') IS NULL EXEC ('CREATE PROCEDURE sp_Table AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_Table
@Name SYSNAME = NULL,
@Schema SYSNAME = NULL,
@IgnoreSchemaList VARCHAR(MAX) = 'cdc,sys,Metadata,tTaxMat,tTaxProp,tTransMat,tTransProp',
@IgnoreSchemaDelimiter VARCHAR(30) = ',',
@WildCharName BIT = 0,
@PrintScript BIT = 1,
@ScriptMostNumberOfObjects INT = 20,
@GetMostRecent INT = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ANSI_WARNINGS OFF
IF @Name IS NULL
AND @Schema IS NULL
AND @GetMostRecent IS NULL
AND ISNULL(@IgnoreSchemaList, '') = 'cdc,sys,Metadata,tTaxMat,tTaxProp,tTransMat,tTransProp'
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_Table',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
DECLARE @RowCount INT
IF @WildCharName = 1
AND @Name NOT LIKE '%[%]%'
SET @Name = '%' + Util.dbo.TrimBothEnds(@Name) + '%'
SET @IgnoreSchemaDelimiter = ISNULL(NULLIF(Util.dbo.TrimBothEnds(@IgnoreSchemaDelimiter), ''), ',')
IF ISNULL(@ScriptMostNumberOfObjects, 0) = 0
SET @ScriptMostNumberOfObjects = 9999999999
DECLARE @Top INT = ISNULL(NULLIF(@GetMostRecent, 0), 99999999),
@OBJECT_ID INT = OBJECT_ID(Util.dbo.TrimBothEnds(@Name))
IF OBJECT_ID('TEMPDB..#ObjectList') IS NOT NULL
DROP TABLE #ObjectList
SELECT TOP (@Top)
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS FQN,
s.name AS SchemaName,
t.name,
cc.ColCnt,
pc.PKColCnt,
p.[Rows],
p.EmptyPart,
p.PartMaxRows,
p.Compression,
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.name AS DataSpace,
ds.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DataSpaceDesc,
HasIdent,
TriggerCount,
FKRefCount,
FKCount,
HasClustInd,
NonClustIndCnt,
HasPrimaryKey,
HasUniqueKey,
UniqueIndexCnt,
t.lob_data_space_id AS LobDataSpaceId,
dl.name COLLATE SQL_Latin1_General_CP1_CI_AS AS LobDataSpace,
t.filestream_data_space_id AS FilestreamDataSpaceId,
df.name COLLATE SQL_Latin1_General_CP1_CI_AS AS FilestreamData,
t.max_column_id_used AS MaxColumnIdUsed,
t.lock_on_bulk_load AS LockOnBulkLoad,
t.uses_ansi_nulls AS UsesAnsiNulls,
t.is_replicated AS IsReplicated,
t.has_replication_filter AS HasReplicationFilter,
t.is_merge_published AS IsMergePublished,
t.is_sync_tran_subscribed AS IsSyncTranSubscribed,
t.has_unchecked_assembly_data AS HasUncheckedAssemblyData,
t.text_in_row_limit AS TextInRowLimit,
t.large_value_types_out_of_row AS LargeValueTypesOutOfRow,
t.is_tracked_by_cdc AS IsTrackedByCdc,
t.lock_escalation AS LockEscalation,
t.lock_escalation_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS LockEscalationDesc,
t.create_date AS CreateDate,
t.modify_date AS ModifyDate,
t.OBJECT_ID
INTO #ObjectList
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
LEFT OUTER JOIN sys.data_spaces dl (NOLOCK) ON dl.data_space_id = t.lob_data_space_id
LEFT OUTER JOIN sys.data_spaces df (NOLOCK) ON df.data_space_id = t.filestream_data_space_id
INNER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = t.OBJECT_ID
AND i.index_id <= 1
LEFT OUTER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = i.data_space_id
CROSS APPLY (SELECT COUNT(*) AS ColCnt,
SUM(CASE WHEN c.is_identity = 1 THEN 1
ELSE 0
END) AS HasIdent
FROM sys.columns c (NOLOCK)
WHERE c.OBJECT_ID = t.OBJECT_ID) cc
CROSS APPLY (SELECT COUNT (*) AS TriggerCount FROM sys.triggers tr (NOLOCK) WHERE tr.parent_id = t.OBJECT_ID) tc
CROSS APPLY (SELECT SUM(CASE WHEN referenced_object_id = ol.OBJECT_ID THEN 1
ELSE 0
END) AS FKRefCount,
SUM(CASE WHEN fk.parent_object_id = ol.OBJECT_ID THEN 1
ELSE 0
END) AS FKCount
FROM sys.foreign_keys fk (NOLOCK)
INNER JOIN sys.tables ol ON referenced_object_id = ol.OBJECT_ID
OR fk.parent_object_id = ol.OBJECT_ID
WHERE ol.OBJECT_ID = t.OBJECT_ID) fks
CROSS APPLY (SELECT SUM(CASE WHEN index_id > 1 THEN 1
ELSE 0
END) AS NonClustIndCnt,
COUNT(DISTINCT CASE WHEN index_id = 1 THEN index_id
END) AS HasClustInd,
COUNT(DISTINCT CASE WHEN is_primary_key = 1 THEN 1
END) HasPrimaryKey,
COUNT(DISTINCT CASE WHEN is_unique_constraint = 1 THEN is_unique_constraint
END) HasUniqueKey,
SUM(CASE WHEN is_unique = 1 THEN 1
ELSE 0
END) UniqueIndexCnt
FROM sys.indexes i (NOLOCK)
WHERE i.OBJECT_ID = t.OBJECT_ID) ix
CROSS APPLY (SELECT COUNT(*) PKColCnt
FROM sys.indexes i (NOLOCK)
INNER JOIN sys.index_columns ic (NOLOCK) ON ic.index_id = i.index_id
AND ic.OBJECT_ID = i.OBJECT_ID
WHERE i.OBJECT_ID = t.OBJECT_ID
AND i.is_primary_key = 1) pc
CROSS APPLY (SELECT COUNT(*) AS PartCnt,
SUM(p.ROWS) AS [Rows],
SUM(CASE WHEN p.ROWS = 0 THEN 1
ELSE 0
END) AS EmptyPart,
MAX(p.ROWS) AS PartMaxRows,
Util.dbo.StringConcat(DISTINCT p.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS, ', ') AS Compression
FROM sys.partitions p (NOLOCK)
WHERE p.OBJECT_ID = i.OBJECT_ID
AND p.index_id = i.index_id) p
WHERE t.is_ms_shipped = 0
AND ((@OBJECT_ID IS NOT NULL
AND t.OBJECT_ID = @OBJECT_ID)
OR ((@OBJECT_ID IS NULL
AND t.is_ms_shipped = 0)
AND NOT EXISTS ( SELECT *
FROM Util.dbo.ParseDelimited(@IgnoreSchemaList, @IgnoreSchemaDelimiter)
WHERE Field = s.name )
AND ((@Name IS NULL
OR (@Name IS NOT NULL
AND @WildCharName = 1
AND t.name LIKE @Name)
OR (@Name IS NOT NULL
AND @WildCharName = 0
AND CHARINDEX(@Name, t.NAME, 0) > 0))
AND (@Schema IS NULL
OR s.name LIKE @Schema))))
ORDER BY CASE WHEN @Top > 0 THEN modify_date
ELSE GETDATE()
END DESC,
s.NAME,
t.name
OPTION (RECOMPILE)
SET @RowCount = @@ROWCOUNT
IF @RowCount BETWEEN 1 AND @ScriptMostNumberOfObjects
AND @PrintScript = 1
BEGIN
EXEC dbo.sp_script
@CreateTable = 1,
@CreateDefaultConstraints = 1,
@CreatePrimaryKey = 1,
@CreateUniqueKey = 1,
@CreateIndex = 1,
@CreateTrigger = 1,
@CreateForeignKey = 1,
@CreateCheckConstraint = 1,
@IncludeSchemaBoundObjects = 1,
@AlterProcedures = 1,
@CreateSETANSI = 1,
@SortInTempDb = 1,
@GenerateIfNotExists = 0,
@UseGOBatchTerminator = 1,
@PrintSQL = 1,
@RtrimLines = 1
END
IF @RowCount > 0
SELECT *
FROM #ObjectList
GO
EXEC sys.sp_ms_marksystemobject sp_Table
GO