/* submitted by: fperkins at: www.SqlServerCentral.com */ DECLARE @table_id int DECLARE @TableName varchar(300) DECLARE @strHTML varchar(8000) DECLARE @strHTML1 varchar(8000) DECLARE @ColumnName varchar(200) DECLARE @ColumnType varchar(200) DECLARE @ColumnLength smallint DECLARE @ColumnComments sql_variant DECLARE @ColumnPrec smallint DECLARE @ColumnScale int DECLARE @ColumnCollation varchar(200) DECLARE @CType sysname DECLARE @CName sysname DECLARE @CPKTable sysname DECLARE @CPKColumn sysname DECLARE @CFKTable sysname DECLARE @CFKColumn sysname DECLARE @CKey smallint DECLARE @CDefault varchar(4000) DECLARE @Populated bit DECLARE @IDesc varchar(60) DECLARE @IRows varchar(11) DECLARE @IReserved varchar(11) DECLARE @IData varchar(11) DECLARE @IIndex varchar(11) DECLARE @IRowData varchar(11) DECLARE @SetOption bit DECLARE @databasename varchar(30) DECLARE @orderCol varchar(30) DECLARE @numeric bit DECLARE @Trigger varchar(50) DECLARE @DBPath varchar(500) DECLARE @ViewName varchar(200) DECLARE @ViewTableDep varchar(200) DECLARE @ViewColDep varchar(200) DECLARE @ViewColDepType varchar(200) DECLARE @ViewColDepLength smallint DECLARE @ViewColDepPrec smallint DECLARE @ViewColDepScale int DECLARE @ViewColDepCollation varchar(200) DECLARE @SPName varchar(200) DECLARE @SPTableDep varchar(200) DECLARE @SPColDep varchar(200) DECLARE @SPColDepType varchar(200) DECLARE @SPColDepLength smallint DECLARE @SPColDepPrec smallint DECLARE @SPColDepScale int DECLARE @SPColDepCollation varchar(200) DECLARE @ParamName sysname DECLARE @ParamDataType varchar(50) DECLARE @ParamType varchar(11) DECLARE @DBLastBackup smalldatetime DECLARE @DBLastBackupDays int DECLARE @UserLogin varchar(30) DECLARE @UserName varchar(30) DECLARE @UserGroup varchar(30) --initialize HTML string SET @strHTML = '' SELECT @strHTML = @strHTML + '
Table Of Contents | |||||
Table | Row Count | Reserved | Row Data | Index Size | Table Data |
Server Options | |||||
Database Options | |||||
Database Users | |||||
' + ISNULL(@IDesc, ' ') + ' | ' + ISNULL(@IRows, ' ') + ' | ' + ISNULL(@IReserved, ' ') + ' | ' + ISNULL(@IData, ' ') + ' | ' + ISNULL(@IIndex, ' ') + ' | ' + ISNULL(@IRowData, ' ') + ' |
' + ISNULL(@ViewName, ' ') + ' | |||||
' + ISNULL(@SPName, ' ') + ' |
Server Settings | |
Table | Row Count |
Server Name | ' + convert(varchar(30),@@SERVERNAME) + ' |
Instance | ' + convert(varchar(30),@@SERVICENAME) + ' |
Current Date Time | ' + convert(varchar(30),getdate(),113) + ' |
User | ' + USER_NAME() + ' |
Number of connections | ' + convert(varchar(30),@@connections) + ' |
Language | ' + convert(varchar(30),@@language) + ' |
Language Id | ' + convert(varchar(30),@@langid) + ' |
Lock Timeout | ' + convert(varchar(30),@@LOCK_TIMEOUT) + ' |
Maximum of connections | ' + convert(varchar(30),@@MAX_CONNECTIONS) + ' |
CPU Busy | ' + convert(varchar(30),@@CPU_BUSY/1000) + ' |
CPU Idle | ' + convert(varchar(30),@@IDLE/1000) + ' |
IO Busy | ' + convert(varchar(30),@@IO_BUSY/1000) + ' |
Packets received | ' + convert(varchar(30),@@PACK_RECEIVED) + ' |
Packets sent | ' + convert(varchar(30),@@PACK_SENT) + ' |
Packets w errors | ' + convert(varchar(30),@@PACKET_ERRORS) + ' |
TimeTicks | ' + convert(varchar(30),@@TIMETICKS) + ' |
IO Errors | ' + convert(varchar(30),@@TOTAL_ERRORS) + ' |
Total Read | ' + convert(varchar(30),@@TOTAL_READ) + ' |
Total Write | ' + convert(varchar(30),@@TOTAL_WRITE) + ' |
Database Settings | |
Option | Setting |
Name | ' + [name] + ' |
autoclose | ' + MIN(CASE status & 1 WHEN 1 THEN 'True' ELSE 'False' END) + ' |
select into/bulkcopy | ' + MIN(CASE status & 4 WHEN 4 THEN 'True' ELSE 'False' END) + ' |
trunc. log on chkpt | ' + MIN(CASE status & 8 WHEN 8 THEN 'True' ELSE 'False' END) + ' |
torn page detection | ' + MIN(CASE status & 16 WHEN 16 THEN 'True' ELSE 'False' END) + ' |
loading | ' + MIN(CASE status & 32 WHEN 32 THEN 'True' ELSE 'False' END) + ' |
pre recovery | ' + MIN(CASE status & 64 WHEN 64 THEN 'True' ELSE 'False' END) + ' |
recovering | ' + MIN(CASE status & 128 WHEN 128 THEN 'True' ELSE 'False' END) + ' |
Falset recovered | ' + MIN(CASE status & 256 WHEN 256 THEN 'True' ELSE 'False' END) + ' |
offline | ' + MIN(CASE status & 512 WHEN 512 THEN 'True' ELSE 'False' END) + ' |
read only | ' + MIN(CASE status & 1024 WHEN 1024 THEN 'True' ELSE 'False' END) + ' |
dbo use only | ' + min(CASE status & 2048 WHEN 2048 THEN 'True' ELSE 'False' END) + ' |
single user | ' + MIN(CASE status & 4096 WHEN 4096 THEN 'True' ELSE 'False' END) + ' |
emergency mode | ' + MIN(CASE status & 32768 WHEN 32768 THEN 'True' ELSE 'False' END) + ' |
autoshrink | ' + MIN(CASE status & 4194304 WHEN 4194304 THEN 'True' ELSE 'False' END) + ' |
cleanly shutdown | ' + MIN(CASE status & 1073741824 WHEN 1073741824 THEN 'True' ELSE 'False' END) + ' |
ANSI null default | ' + MIN(CASE status2 & 16384 WHEN 16384 THEN 'True' ELSE 'False' END) + ' |
concat null yields null | ' + MIN(CASE status2 & 65536 WHEN 65536 THEN 'True' ELSE 'False' END) + ' |
recursive triggers | ' + MIN(CASE status2 & 131072 WHEN 131072 THEN 'True' ELSE 'False' END) + ' |
default to local cursor | ' + MIN(CASE status2 & 1048576 WHEN 1048576 THEN 'True' ELSE 'False' END) + ' |
quoted identifier | ' + MIN(CASE status2 & 8388608 WHEN 8388608 THEN 'True' ELSE 'False' END) + ' |
cursor close on commit | ' + MIN(CASE status2 & 33554432 WHEN 33554432 THEN 'True' ELSE 'False' END) + ' |
ANSI nulls | ' + MIN(CASE status2 & 67108864 WHEN 67108864 THEN 'True' ELSE 'False' END) + ' |
ANSI warnings | ' + MIN(CASE status2 & 268435456 WHEN 268435456 THEN 'True' ELSE 'False' END) + ' |
full text enabled | ' + MIN(CASE status2 & 536870912 WHEN 536870912 THEN 'True' ELSE 'False' END) + ' |
Data Path | ' + @DBPath + ' |
Last Backup | ' + ISNULL(CONVERT(varchar(50),@DBLastBackup),' ') + ' |
Days Since Last Backup | ' + ISNULL(CONVERT(varchar(10),@DBLastBackupDays),' ') + ' |
Users | |||||
Login Name | User Name | Group Name | |||
' + ISNULL(@UserLogin, ' ') + ' | ' + ISNULL(@UserName, ' ') + ' | ' + ISNULL(@UserGroup, ' ') + ' |
' + sysobjects.name + ' | ||||||
Column | Type | Length | Precision | Scale | Collation | Comments |
' + @ColumnName + ' | ' + ISNULL(@ColumnType, ' ') + ' | ' + ISNULL(convert(varchar(5), @ColumnLength), ' ') + ' | ' + ISNULL(convert(varchar(5), @ColumnPrec), ' ') + ' | ' + ISNULL(convert(varchar(5), @ColumnScale), ' ') + ' | ' + ISNULL(@ColumnCollation, ' ') + ' | ' + ISNULL(convert(varchar(500), @ColumnComments), ' ') + ' |
Constraints | |||||||
Constraint Type | Contraint Name | Table | Column | FK Table | FK Column | Key No. | Default |
' + ISNULL(@CType, ' ') + ' | ' + ISNULL(@CName, ' ') + ' | ' + ISNULL(convert(varchar(120), @CPKTable), ' ') + ' | ' + ISNULL(convert(varchar(120), @CPKColumn), ' ') + ' | ' + ISNULL(convert(varchar(120), @CFKTable), ' ') + ' | ' + ISNULL(convert(varchar(120), @CFKColumn), ' ') + ' | ' + ISNULL(convert(varchar(5), @CKey), ' ') + ' | ' + ISNULL(convert(varchar(20), @CDefault), ' ') + ' |
Triggers |
' + ISNULL(@Trigger, ' ') + ' |
' + @ViewName + ' | ||||||
Table Dependencies | Column Dependencies | Column Type | Size | Precision | Scale | Collation |
' + ISNULL(convert(varchar(200), @ViewTableDep), ' ') + ' | ' + ISNULL(convert(varchar(200), @ViewColDep), ' ') + ' | ' + ISNULL(@ViewColDepType, ' ') + ' | ' + ISNULL(convert(varchar(5), @ViewColDepLength), ' ') + ' | ' + ISNULL(convert(varchar(5), @ViewColDepPrec), ' ') + ' | ' + ISNULL(convert(varchar(5), @ViewColDepScale), ' ') + ' | ' + ISNULL(@ViewColDepCollation, ' ') + ' |
| ||||||||||||
Table Dependencies | Column Dependencies | Column Type | Size | Precision | Scale | Collation | ||||||
' + ISNULL(convert(varchar(200), @SPTableDep), ' ') + ' | ' + ISNULL(convert(varchar(200), @SPColDep), ' ') + ' | ' + ISNULL(@SPColDepType, ' ') + ' | ' + ISNULL(convert(varchar(5), @SPColDepLength), ' ') + ' | ' + ISNULL(convert(varchar(5), @SPColDepPrec), ' ') + ' | ' + ISNULL(convert(varchar(5), @SPColDepScale), ' ') + ' | ' + ISNULL(@SPColDepCollation, ' ') + ' |