Problem Source Identification Assistance, Please
-
Bear with me, please... I obtained the following script from another thread in this forum -- the OP stating they did not know whom the original author to be. Well, I cleaned it up quite a bit (mostly adjustments to the generated HTML) and corrected a few SQL errors that were showing up when this script was run against my database.
However, there is one problem with it that I haven't yet been able to resolve. This problem has to do with listing the constraints for each data table (the pertinent section of the script is highlighted in red). As shown by the table definitions, posted after the script, I believe I should see the following:
tabledef 1 has primary key and unique constraints
tabledef 2 has primary key and foreign key constraints
tabledef 3 has primary key and foreign key constraints plus a non-unique index definition
Now, when I run this script against my database, the first problem is that it only shows the foreign key constraints for table definitions 2 and 3 -- both as key number 1. The second problem is that it shows two primary key constraints and a unique constraint for table definition 1 -- and all as key number 1.
For clarity, the generated HTML can be viewed here:
http://www.DaveClarkConsulting.com/sp_describe.html
Now, I think I understand that it is showing the primary key constraint twice for table definition 1 because there are two foreign key relationships against this primary key. The fact that all three show as key number 1, though, is a bit confusing. Then, I'd like to know why the primary key constraints don't show for table definitions 1 & 2 -- and why the non-unique index definition for table definition 3 doesn't show.
Any takers?
[script]
/*
Original by: Unknown
Modified by: Dave Clark Consulting
E-mail: webmaster@daveclarkconsulting.com
Website: http://www.DaveClarkConsulting.com
Location: Dayton, OH, USA
*/
DECLARE @table_id int
DECLARE @TableName varchar(300)
DECLARE @strHTML 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)
SET NOCOUNT ON
--
-- build HTML page heading
--
PRINT '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<HTML>
<HEAD>
<TITLE>' + db_Name() + ' Database Definition</TITLE>
<STYLE>
BODY {
FONT-FAMILY: Tahoma;
FONT-SIZE: 8pt;
}
TABLE {
BORDER-COLLAPSE: Collapse;
BORDER-BOTTOM: 1pt solid #003366;
BORDER-LEFT: 0pt solid #003366;
BORDER-RIGHT: 1pt solid #003366;
BORDER-TOP: 0pt solid #003366;
FONT-FAMILY: Tahoma;
FONT-SIZE: 8pt;
}
TR {
BORDER: 0pt solid #003366;
MARGIN: 0pt;
PADDING: 0pt;
}
TD {
BORDER-BOTTOM: 0pt solid #003366;
BORDER-LEFT: 1pt solid #003366;
BORDER-RIGHT: 0pt solid #003366;
BORDER-TOP: 1pt solid #003366;
FONT-FAMILY: Tahoma;
FONT-SIZE: 8pt;
MARGIN: 0pt;
PADDING: 2pt;
TEXT-ALIGN: Left;
VERTICAL-ALIGN: Top;
}
TD.Title {
FONT-WEIGHT: bold;
FONT-FAMILY: Tahoma;
FONT-SIZE: 12pt;
TEXT-ALIGN: Center;
VERTICAL-ALIGN: Bottom;
}
TD.Sub {
FONT-WEIGHT: bold;
BORDER-TOP: 0pt solid #003366;
TEXT-ALIGN: Center;
VERTICAL-ALIGN: Bottom;
}
TD.Num {
TEXT-ALIGN: Right;
}
A.Index {
FONT-WEIGHT: bold;
FONT-SIZE: 8pt;
COLOR: #000099;
FONT-FAMILY: Tahoma;
TEXT-DECORATION: none;
}
A.Index:HOVER {
TEXT-DECORATION: none;
}
</STYLE>
</HEAD>
<BODY>
<p><A NAME="_top"></A> </p>
<CENTER>
<p><FONT SIZE="5"><B>' + db_name() + ' Database Definition</B></FONT></p>
</CENTER>
<CENTER>
<p><A HREF="#_ServerOptions" CLASS="Index">SERVER SETTINGS<A> |
<A HREF="#_Options" CLASS="Index">DATABASE SETTINGS<A> |
<A HREF="#_Users" CLASS="Index">USERS<A> |
<A HREF="#_Tables" CLASS="Index">TABLES<A> |
<A HREF="#_Views" CLASS="Index">VIEWS<A> |
<A HREF="#_SP" CLASS="Index">STORED PROCEDURES<A></p>
</CENTER>
'
--
-- setup
--
SET @orderCol = 'Description'
SET @DatabaseName = db_name()
SET @numeric = 1
IF @DatabaseName <> 'Master'
AND @DatabaseName <> 'master'
AND NOT EXISTS (select 1 from master..sysdatabases WHERE name = @DatabaseName AND (status & 4) = 4)
BEGIN
exec sp_dboption @databaseName ,'select into/bulkcopy', 'true'
SET @SetOption = 1
END
IF EXISTS (SELECT 1 FROM master..sysobjects WHERE name = 'space1')
DROP TABLE master..space1
CREATE TABLE master..Space1 (
name varchar(60),
rows varchar(11),
reserved varchar(11),
data varchar(11),
index_size varchar(11),
unused varchar(11)
)
DECLARE @Cmd varchar(255)
DECLARE cSpace CURSOR FOR
SELECT 'USE ' + @DatabaseName + ' INSERT into master..space1 EXEC sp_spaceUsed ''[' + u.name + '].[' + o.name + ']'''
FROM sysobjects o
JOIN sysusers u
ON u.uid = o.uid
WHERE type = 'U'
AND o.Name <> 'Space1'
OPEN cSPACE
FETCH cSpace INTO @Cmd
WHILE @@FETCH_STATUS =0
BEGIN
-- PRINT '<p>' + @Cmd + '</p>'
EXECUTE (@Cmd)
FETCH cSpace INTO @Cmd
END
CLOSE cSPACE
DEALLOCATE cSPace
--
-- build Table Of Contents
--
PRINT '
<DIV ALIGN="center">
<TABLE WIDTH="80%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Title" COLSPAN="6"><B>Table Of Contents</B> </TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="50%"><B>Table Entry</B> </TD>
<TD CLASS="Sub" WIDTH="10%"><B>Row Count</B> </TD>
<TD CLASS="Sub" WIDTH="10%"><B>Reserved</B> </TD>
<TD CLASS="Sub" WIDTH="10%"><B>Row Data</B> </TD>
<TD CLASS="Sub" WIDTH="10%"><B>Index Size</B> </TD>
<TD CLASS="Sub" WIDTH="10%"><B>Table Data</B> </TD>
</TR>
<TR>
<TD><A CLASS="Index" HREF="#_ServerOptions">Server Options</A> </TD>
<TD BGCOLOR="EEEEEE" COLSPAN="5"> </TD>
</TR>
<TR>
<TD><A CLASS="Index" HREF="#_Options">Database Options</A> </TD>
<TD BGCOLOR="EEEEEE" COLSPAN="5"> </TD>
</TR>
<TR>
<TD><A CLASS="Index" HREF="#_Users">Database Users</A> </TD>
<TD BGCOLOR="EEEEEE" COLSPAN="5"> </TD>
</TR>
'
/* data table entries */
DECLARE cursor_index CURSOR FOR
SELECT Description,Rows,Reserved,Data,Index_size,dataPerRows
FROM (
SELECT 3 DataOrder,
CONVERT(int,CASE @OrderCol
WHEN 'Rows' THEN Rows
WHEN 'Reserved' THEN SUBSTRING(Reserved, 1,LEN(Reserved)-2)
WHEN 'data' THEN SUBSTRING(Data, 1,LEN(Data)-2)
WHEN 'index_size' THEN SUBSTRING(Index_size, 1,LEN(index_Size)-2)
WHEN 'unused' THEN SUBSTRING(unused, 1,LEN(unused)-2)
END) OrderData,
name Description,
rows,
CASE @NUMERIC
WHEN 0 THEN reserved
ELSE SUBSTRING(reserved, 1, len(reserved)-2)
END reserved,
CASE @NUMERIC
WHEN 0 THEN data
ELSE SUBSTRING(data, 1, len(data)-2)
END data,
CASE @NUMERIC
WHEN 0 THEN index_size
ELSE SUBSTRING(index_size, 1, len(index_size)-2)
END index_size,
CASE
WHEN Rows = 0 THEN '0'
ELSE CONVERT(varchar(11),CONVERT(numeric(10,2),CONVERT(numeric,SUBSTRING(reserved, 1, len(reserved)-2)) /rows*1000))
END DataPerRows
FROM master..Space1 ) Stuff
ORDER BY DataOrder, OrderData desc, description
OPEN cursor_index
FETCH NEXT FROM cursor_index INTO @IDesc,@IRows,@IReserved,@IData,@IIndex,@IRowData
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<TR>
<TD><A CLASS="Index" HREF="#' + ISNULL(@IDesc, ' ') + '">' + ISNULL(@IDesc, ' ') + '</A> </TD>
<TD CLASS="Num">' + ISNULL(@IRows, ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(@IReserved, ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(@IData, ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(@IIndex, ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(@IRowData, ' ') + ' </TD>
</TR>
'
FETCH NEXT FROM cursor_index INTO @IDesc,@IRows,@IReserved,@IData,@IIndex,@IRowData
END
CLOSE cursor_index
DEALLOCATE cursor_index
/* data view entries */
DECLARE cursor_views_index CURSOR FOR
SELECT [name] FROM sysobjects WHERE [xtype] = 'V' AND [category] <> 2 ORDER BY [name]
OPEN cursor_views_index
FETCH NEXT FROM cursor_views_index INTO @ViewName
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<TR>
<TD><A CLASS="Index" HREF="#' + ISNULL(@ViewName, ' ') + '">' + ISNULL(@ViewName, ' ') + '</A> </TD>
<TD BGCOLOR="EEEEEE" COLSPAN="5"> </TD>
</TR>
'
FETCH NEXT FROM cursor_views_index INTO @ViewName
END
CLOSE cursor_views_index
DEALLOCATE cursor_views_index
/* stored procedure entries */
DECLARE cursor_sp_index CURSOR FOR
SELECT [name] FROM sysobjects WHERE [xtype] = 'P' AND [category] <> 2 ORDER BY [name]
OPEN cursor_sp_index
FETCH NEXT FROM cursor_sp_index INTO @SPName
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<TR>
<TD><A CLASS="Index" HREF="#' + ISNULL(@SPName, ' ') + '">' + ISNULL(@SPName, ' ') + '</A> </TD>
<TD BGCOLOR="EEEEEE" COLSPAN="5"> </TD>
</TR>
'
FETCH NEXT FROM cursor_sp_index INTO @SPName
END
CLOSE cursor_sp_index
DEALLOCATE cursor_sp_index
PRINT '
</TABLE>
</DIV>
<p> </p>
'
--
-- build server settings documentation
--
EXECUTE ('DROP TABLE master..space1')
IF @SetOption = 1 exec sp_dboption @databasename ,'select into/bulkcopy', 'false'
PRINT '
<DIV ALIGN="center">
<TABLE WIDTH="60%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Title" COLSPAN="2"><B><A NAME="_ServerOptions">Server Settings</A></B> </TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="30%"><B>Option</B> </TD>
<TD CLASS="Sub" WIDTH="70%"><B>Setting</B> </TD>
</TR>
<TR><TD><B>Server Name </B></TD><TD>' + convert(varchar(30),@@SERVERNAME) + ' </TD></TR>
<TR><TD><B>Instance </B></TD><TD>' + convert(varchar(30),@@SERVICENAME) + ' </TD></TR>
<TR><TD><B>Current Date Time </B></TD><TD>' + convert(varchar(30),getdate(),113) + ' </TD></TR>
<TR><TD><B>User </B></TD><TD>' + USER_NAME() + ' </TD></TR>
<TR><TD><B>Number of connections </B></TD><TD>' + convert(varchar(30),@@connections) + ' </TD></TR>
<TR><TD><B>Language </B></TD><TD>' + convert(varchar(30),@@language) + ' </TD></TR>
<TR><TD><B>Language Id </B></TD><TD>' + convert(varchar(30),@@langid) + ' </TD></TR>
<TR><TD><B>Lock Timeout </B></TD><TD>' + convert(varchar(30),@@LOCK_TIMEOUT) + ' </TD></TR>
<TR><TD><B>Maximum of connections </B></TD><TD>' + convert(varchar(30),@@MAX_CONNECTIONS) + ' </TD></TR>
<TR><TD><B>CPU Busy </B></TD><TD>' + convert(varchar(30),@@CPU_BUSY/1000) + ' </TD></TR>
<TR><TD><B>CPU Idle </B></TD><TD>' + convert(varchar(30),@@IDLE/1000) + ' </TD></TR>
<TR><TD><B>IO Busy </B></TD><TD>' + convert(varchar(30),@@IO_BUSY/1000) + ' </TD></TR>
<TR><TD><B>Packets received </B></TD><TD>' + convert(varchar(30),@@PACK_RECEIVED) + ' </TD></TR>
<TR><TD><B>Packets sent </B></TD><TD>' + convert(varchar(30),@@PACK_SENT) + ' </TD></TR>
<TR><TD><B>Packets w errors </B></TD><TD>' + convert(varchar(30),@@PACKET_ERRORS) + ' </TD></TR>
<TR><TD><B>TimeTicks </B></TD><TD>' + convert(varchar(30),@@TIMETICKS) + ' </TD></TR>
<TR><TD><B>IO Errors </B></TD><TD>' + convert(varchar(30),@@TOTAL_ERRORS) + ' </TD></TR>
<TR><TD><B>Total Read </B></TD><TD>' + convert(varchar(30),@@TOTAL_READ) + ' </TD></TR>
<TR><TD><B>Total Write </B></TD><TD>' + convert(varchar(30),@@TOTAL_WRITE) + ' </TD></TR>
</TABLE>
</DIV>
<p><A CLASS="Index" HREF="#_top">Back To Top ^</A></p>
'
--
-- build database settings documentation
--
PRINT '
<DIV ALIGN="center">
<TABLE WIDTH="60%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Title" COLSPAN="2"><A NAME="_Options"><B>Database Settings</B></A> </TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="30%"><B>Option</B> </TD>
<TD CLASS="Sub" WIDTH="70%"><B>Setting</B> </TD>
</TR>
'
SELECT @strHTML = '
<TR><TD><B>Name </B></TD><TD>' + [name] + ' </TD></TR>
<TR><TD><B>autoclose </B></TD><TD>' + MIN(CASE status & 1 WHEN 1 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>select into/bulkcopy </B></TD><TD>' + MIN(CASE status & 4 WHEN 4 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>trunc. log on chkpt </B></TD><TD>' + MIN(CASE status & 8 WHEN 8 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>torn page detection </B></TD><TD>' + MIN(CASE status & 16 WHEN 16 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>loading </B></TD><TD>' + MIN(CASE status & 32 WHEN 32 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>pre recovery </B></TD><TD>' + MIN(CASE status & 64 WHEN 64 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>recovering </B></TD><TD>' + MIN(CASE status & 128 WHEN 128 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>Falset recovered </B></TD><TD>' + MIN(CASE status & 256 WHEN 256 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>offline </B></TD><TD>' + MIN(CASE status & 512 WHEN 512 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>read only </B></TD><TD>' + MIN(CASE status & 1024 WHEN 1024 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>dbo use only </B></TD><TD>' + MIN(CASE status & 2048 WHEN 2048 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>single user </B></TD><TD>' + MIN(CASE status & 4096 WHEN 4096 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>emergency mode </B></TD><TD>' + MIN(CASE status & 32768 WHEN 32768 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>autoshrink </B></TD><TD>' + MIN(CASE status & 4194304 WHEN 4194304 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>cleanly shutdown </B></TD><TD>' + MIN(CASE status & 1073741824 WHEN 1073741824 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>ANSI null default </B></TD><TD>' + MIN(CASE status2 & 16384 WHEN 16384 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>concat null yields null </B></TD><TD>' + MIN(CASE status2 & 65536 WHEN 65536 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>recursive triggers </B></TD><TD>' + MIN(CASE status2 & 131072 WHEN 131072 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>default to local cursor </B></TD><TD>' + MIN(CASE status2 & 1048576 WHEN 1048576 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>quoted identifier </B></TD><TD>' + MIN(CASE status2 & 8388608 WHEN 8388608 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>cursor close on commit </B></TD><TD>' + MIN(CASE status2 & 33554432 WHEN 33554432 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>ANSI nulls </B></TD><TD>' + MIN(CASE status2 & 67108864 WHEN 67108864 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>ANSI warnings </B></TD><TD>' + MIN(CASE status2 & 268435456 WHEN 268435456 THEN 'True' ELSE 'False' END) + ' </TD></TR>
<TR><TD><B>full text enabled </B></TD><TD>' + MIN(CASE status2 & 536870912 WHEN 536870912 THEN 'True' ELSE 'False' END) + ' </TD></TR>
'
FROM master..sysdatabases
WHERE [name] = db_Name()
GROUP BY [name]
PRINT @strHTML
SET @DBPath = (SELECT [filename] FROM master..sysdatabases WHERE [name] = db_Name())
PRINT '
<TR>
<TD><B>Data Path</B> </TD>
<TD>' + @DBPath + ' </TD>
</TR>
'
SET @DBLastBackup = (SELECT CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) FROM MSDB.dbo.BackupSet WHERE Type = 'd' AND Database_Name = db_Name())
PRINT '
<TR>
<TD><B>Last Backup</B> </TD>
<TD>' + ISNULL(CONVERT(varchar(50),@DBLastBackup), ' ') + ' </TD>
</TR>
'
SET @DBLastBackupDays = (SELECT DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) FROM MSDB.dbo.BackupSet WHERE Type = 'd' AND Database_Name = db_Name())
PRINT '
<TR>
<TD><B>Days Since Last Backup</B> </TD>
<TD>' + ISNULL(CONVERT(varchar(10),@DBLastBackupDays), ' ') + ' </TD>
</TR>
'
PRINT '
</TABLE>
</DIV>
<p><A CLASS="Index" HREF="#_top">Back To Top ^</A></p>
'
--
-- build users documentation
--
PRINT '
<DIV ALIGN="center">
<TABLE WIDTH="60%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Title" COLSPAN="6"><A NAME="_Users"><B>Users</B></A> </TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="40%"><B>Login Name</B> </TD>
<TD CLASS="Sub" WIDTH="30%"><B>User Name</B> </TD>
<TD CLASS="Sub" WIDTH="30%"><B>Group Name</B> </TD>
</TR>
'
DECLARE cursor_users CURSOR FOR
SELECT LEFT(rtrim(CASE u1.islogin WHEN 1 THEN u1.name END), 30), LEFT(rtrim(u1.name), 30), LEFT(rtrim(u2.name), 30)
FROM sysusers u1, sysusers u2
WHERE u1.gid = u2.uid AND u1.sid IS NOT NULL AND u1.name NOT IN ('guest', 'dbo', 'Administrator')
OPEN cursor_users
FETCH NEXT FROM cursor_users INTO @UserLogin,@UserName,@UserGroup
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<TR>
<TD>' + ISNULL(@UserLogin, ' ') + ' </TD>
<TD>' + ISNULL(@UserName, ' ') + ' </TD>
<TD>' + ISNULL(@UserGroup, ' ') + ' </TD>
</TR>
'
FETCH NEXT FROM cursor_users INTO @UserLogin,@UserName,@UserGroup
END
CLOSE cursor_users
DEALLOCATE cursor_users
PRINT '
</TABLE>
</DIV>
<p><A CLASS="Index" HREF="#_top">Back To Top ^</A></p>
'
--
-- build tables documentation
--
PRINT '
<CENTER>
<FONT SIZE="5"><A NAME="_Tables"><B>Tables</B></A></FONT>
</CENTER>
<p> </p>
'
DECLARE cursor_documentation CURSOR FOR
SELECT DISTINCT id , [name]
FROM sysobjects
WHERE OBJECTPROPERTY(sysobjects.id, 'IsMSShipped') = 0
AND sysobjects.type = 'U'
ORDER BY sysobjects.[name]
OPEN cursor_documentation
FETCH NEXT FROM cursor_documentation INTO @table_id, @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<TABLE WIDTH="100%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Title" COLSPAN="7"><A NAME="' + @TableName + '"><B>' + @TableName + '</B></A> </TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="25%"><B>Column</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Type</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Length</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Precision</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Scale</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Collation</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Comments</B> </TD>
</TR>
'
DECLARE cursor_Column CURSOR FOR
SELECT syscolumns.[name],
(SELECT TOP 1 systypes.[name] FROM systypes WHERE xtype = syscolumns.xtype),
syscolumns.length,
sysproperties.[value],
syscolumns.prec,
syscolumns.scale,
syscolumns.[collation]
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
LEFT OUTER JOIN sysproperties
ON syscolumns.colid = sysproperties.smallid
AND syscolumns.id = sysproperties.id
WHERE sysobjects.id = @table_id
ORDER BY syscolumns.colorder
OPEN cursor_Column
FETCH NEXT FROM cursor_Column INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnComments, @ColumnPrec, @ColumnScale, @ColumnCollation
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<TR>
<TD>' + @ColumnName + ' </TD>
<TD>' + ISNULL(@ColumnType, ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @ColumnLength), ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @ColumnPrec), ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @ColumnScale), ' ') + ' </TD>
<TD>' + ISNULL(@ColumnCollation, ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(500), @ColumnComments), ' ') + ' </TD>
</TR>
'
FETCH NEXT FROM cursor_Column INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnComments, @ColumnPrec, @ColumnScale, @ColumnCollation
END
CLOSE cursor_Column
DEALLOCATE cursor_Column
PRINT '
</TABLE>
'
SET @Populated = 0
SET @strHTML = '
<TABLE WIDTH="100%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" COLSPAN="8"><B>Constraints</B> </TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="10%"><B>Type</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Constraint Name</B> </TD>
<TD CLASS="Sub" WIDTH="15%"><B>Table</B> </TD>
<TD CLASS="Sub" WIDTH="15%"><B>Column</B> </TD>
<TD CLASS="Sub" WIDTH="15%"><B>FK Table</B> </TD>
<TD CLASS="Sub" WIDTH="15%"><B>FK Column</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Key No.</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Default</B> </TD>
</TR>
'
DECLARE cursor_Constraint CURSOR FOR
(SELECT CASE o1.xtype
WHEN 'C' THEN 'Check'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign Key'
WHEN 'PK' THEN 'Primary Key'
WHEN 'UQ' THEN 'Unique'
ELSE 'Other'
END AS 'Constraint Type',
o1.name AS 'Constraint Name',
o.name AS 'Table Name',
c1.name AS 'Column Name',
NULL AS 'FK Table Name',
NULL AS 'FK Column Name',
k.keyno AS 'KeyNo',
NULL AS 'Default / Check Value'
FROM sysobjects o
JOIN sysobjects o1 ON o1.Parent_obj = o.id
JOIN sysconstraints c ON c.constid = o1.id
JOIN sysindexes i ON i.id = o.id AND i.name = o1.name
JOIN sysindexkeys k ON k.id = i.id AND k.indid = i.indid
JOIN syscolumns c1 ON c1.id = k.id AND c1.colid = k.colid
WHERE o1.xtype = 'UQ' AND o.id = @table_id
UNION
SELECT CASE o1.xtype
WHEN 'C' THEN 'Check'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign Key'
WHEN 'PK' THEN 'Primary Key'
WHEN 'UQ' THEN 'Unique'
ELSE 'Other'
END AS 'Constraint Type',
o1.name AS 'Constraint Name',
o.name AS 'Table Name',
c1.name AS 'Column Name',
NULL AS 'FK Table Name',
NULL AS 'FK Column Name',
NULL AS 'KeyNo',
c.text AS 'Default / Check Value'
FROM sysobjects o
JOIN sysobjects o1 ON o1.Parent_obj = o.id
JOIN syscolumns c1 ON c1.id = o1.parent_obj AND c1.colid = o1.info
JOIN syscomments c ON o1.id = c.id
WHERE o1.xtype In ('C' , 'D') AND o.id = @table_id
UNION
SELECT CASE o1.xtype
WHEN 'C' THEN 'Check'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign Key'
WHEN 'PK' THEN 'Primary Key'
WHEN 'UQ' THEN 'Unique'
ELSE 'Other'
END AS 'Constraint Type',
o1.name AS 'Constraint Name',
o.name AS 'FK Table Name',
c1.name AS 'FK Column Name',
o2.name AS 'Table Table',
c2.name AS 'Column Name',
fk.keyno AS 'KeyNo',
NULL AS 'Default/Check Value'
FROM sysobjects o
JOIN sysobjects o1 ON o1.Parent_obj = o.id
JOIN sysforeignkeys fk ON fk.constid = o1.id
JOIN sysobjects o2 ON o2.id = fk.rkeyid
LEFT JOIN syscolumns c1 ON c1.id = fk.fkeyid AND c1.colid = fk.fkey
LEFT JOIN syscolumns c2 ON c2.id = fk.rkeyid AND c2.colid = fk.rkey
WHERE o1.xtype = 'F' AND o.id = @table_id
UNION
SELECT CASE o1.xtype
WHEN 'C' THEN 'Check'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign Key'
WHEN 'PK' THEN 'Primary Key'
WHEN 'UQ' THEN 'Unique'
ELSE 'Other'
END AS 'Constraint Type',
o1.name AS 'Constraint Name',
o.name AS 'Table Name',
c1.name AS 'Column Name',
o2.name AS 'FK Table',
c2.name AS 'FK Column Name',
fk.keyno AS 'KeyNo',
NULL AS 'Default/Check Value'
FROM sysobjects o JOIN sysobjects o1 ON o1.Parent_obj = o.id
JOIN sysforeignkeys fk ON fk.rkeyid = o.id
JOIN sysobjects o2 ON o2.id = fk.fkeyid
LEFT JOIN syscolumns c1 ON c1.id = fk.rkeyid AND c1.colid = fk.rkey
LEFT JOIN syscolumns c2 ON c2.id = fk.rkeyid AND c2.colid = fk.rkey
where o1.xtype = 'PK' AND o.id = @table_id
) ORDER BY [Constraint Type]
OPEN cursor_Constraint
FETCH NEXT FROM cursor_Constraint INTO @CType,@CName,@CPKTable,@CPKColumn,@CFKTable,@CFKColumn,@CKey,@CDefault
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Populated = 0
BEGIN
PRINT @strHTML
END
SET @Populated = 1
PRINT '
<TR>
<TD>' + ISNULL(@CType, ' ') + ' </TD>
<TD>' + ISNULL(@CName, ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(120), @CPKTable), ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(120), @CPKColumn), ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(120), @CFKTable), ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(120), @CFKColumn), ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @CKey), ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(20), @CDefault), ' ') + ' </TD>
</TR>
'
FETCH NEXT FROM cursor_Constraint INTO @CType,@CName,@CPKTable,@CPKColumn,@CFKTable,@CFKColumn,@CKey,@CDefault
END
CLOSE cursor_Constraint
DEALLOCATE cursor_Constraint
IF @Populated = 1
PRINT '
</TABLE>
'
SET @Populated = 0
SET @strHTML = '
<TABLE WIDTH="100%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="10%"><B>Triggers</B> </TD>
</TR>
'
DECLARE cursor_Triggers CURSOR FOR
SELECT [name] AS TriggerName FROM sysobjects WHERE xtype = 'TR' AND parent_obj = @table_id
OPEN cursor_Triggers
FETCH NEXT FROM cursor_Triggers INTO @Trigger
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Populated = 0
BEGIN
PRINT @strHTML
END
SET @Populated = 1
PRINT '
<TR>
<TD>' + ISNULL(@Trigger, ' ') + ' </TD> </TD>
</TR>
'
FETCH NEXT FROM cursor_Triggers INTO @Trigger
END
CLOSE cursor_Triggers
DEALLOCATE cursor_Triggers
IF @Populated = 1
PRINT '
</TABLE>
'
PRINT '
<p><A CLASS="Index" HREF="#_top">Back To Top ^</A></p>
'
FETCH NEXT FROM cursor_documentation INTO @table_id, @TableName
END
CLOSE cursor_documentation
DEALLOCATE cursor_documentation
--
-- build views documentation
--
SET @Populated = 0
PRINT '
<CENTER>
<FONT SIZE="5"><A NAME="_Views"><B>Views</B></A></FONT>
</CENTER>
<p> </p>
'
DECLARE cursor_views CURSOR FOR
SELECT [name] FROM sysobjects WHERE [xtype] = 'V' AND [category] <> 2 ORDER BY [name]
OPEN cursor_views
FETCH NEXT FROM cursor_views INTO @ViewName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Populated = 1
PRINT '
<DIV ALIGN="center">
<TABLE WIDTH="100%">
<TR BGCOLOR="EEEEEE">
<TD CLASS="Title" COLSPAN="7"><A NAME=#' + @ViewName + '><B>' + @ViewName + '</B></A> </TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="20%"><B>Table Dependencies</B> </TD>
<TD CLASS="Sub" WIDTH="25%"><B>Column Dependencies</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Column Type</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Size</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Precision</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Scale</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Collation</B> </TD>
</TR>
'
DECLARE cursor_viewdeps CURSOR FOR
SELECT TableSysObjects.name AS [Table],
col.name AS [Column],
(SELECT TOP 1 systypes.[name] FROM systypes WHERE xtype = col.xtype),
col.length,
col.prec,
col.scale,
col.[collation]
FROM sysobjects ViewSysObjects
LEFT OUTER JOIN sysdepends dep ON ViewSysObjects.id = dep.id
LEFT OUTER JOIN sysobjects TableSysObjects ON dep.depid = TableSysObjects.id
LEFT OUTER JOIN syscolumns col ON dep.depnumber = col.colid AND TableSysObjects.id = col.id
WHERE ViewSysObjects.xtype = 'V'
And ViewSysObjects.category = 0
AND ViewSysObjects.name = @ViewName
ORDER BY ViewSysObjects.name,TableSysObjects.name,col.name
OPEN cursor_viewdeps
FETCH NEXT FROM cursor_viewdeps INTO @ViewTableDep,@ViewColDep,@ViewColDepType,@ViewColDepLength,@ViewColDepPrec,@ViewColDepScale,@ViewColDepCollation
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<TR>
<TD>' + ISNULL(convert(varchar(200), @ViewTableDep), ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(200), @ViewColDep), ' ') + ' </TD>
<TD>' + ISNULL(@ViewColDepType, ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @ViewColDepLength), ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @ViewColDepPrec), ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @ViewColDepScale), ' ') + ' </TD>
<TD>' + ISNULL(@ViewColDepCollation, ' ') + ' </TD>
</TR>
'
FETCH NEXT FROM cursor_viewdeps INTO @ViewTableDep,@ViewColDep,@ViewColDepType,@ViewColDepLength,@ViewColDepPrec,@ViewColDepScale,@ViewColDepCollation
END
CLOSE cursor_viewdeps
DEALLOCATE cursor_viewdeps
PRINT '
</TABLE>
</DIV>
<p><A CLASS="Index" HREF="#_top">Back To Top ^</A></p>
'
FETCH NEXT FROM cursor_views INTO @ViewName
END
CLOSE cursor_views
DEALLOCATE cursor_views
IF @Populated = 0
PRINT '
<p><A CLASS="Index" HREF="#_top">Back To Top ^</A></p>
'
--
-- build stored procedures documentation
--
PRINT '
<CENTER>
<FONT SIZE="5"><A NAME="_SP"><B>Stored Procedures</B></A></FONT>
</CENTER>
<p> </p>
'
DECLARE cursor_sp CURSOR FOR
SELECT [name] FROM sysobjects WHERE [xtype] = 'P' AND [category] <> 2 ORDER BY [name]
OPEN cursor_sp
FETCH NEXT FROM cursor_sp INTO @SPName
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '
<DIV ALIGN="center">
<TABLE WIDTH="100%">
<TR BGCOLOR="#EEEEEE">
<TD CLASS="Title" COLSPAN="7">
<CENTER>
<A NAME=#' + @SPName + '><B>' + @SPName + '</B></A>
</CENTER>
'
SET @Populated = 0
SET @strHTML = '
<FONT SIZE="1"><BR></FONT>
<DIV ALIGN="center">
<TABLE WIDTH="50%">
<TR><TD CLASS="Sub" COLSPAN="3"><B>Parameters</B> </TD></TR>
'
DECLARE cursor_Params CURSOR FOR
SELECT rtrim(c.name) PARAMETER ,
rtrim(convert(varchar (50),d.type_name) +
case
when d.oledb_data_type = 129 /*DBTYPE_STR*/
or d.oledb_data_type = 128 /*DBTYPE_BYTES*/
then '(' + convert(varchar (10),coalesce(d.column_size,c.length)) + ')'
when d.oledb_data_type = 130 /*DBTYPE_WSTR*/
then '(' + convert(varchar(10), coalesce(d.column_size,c.length/2)) + ')'
else''
end ) DATA_TYPE,
case
when c.isoutparam =1 then 'Output'
else 'Input '
end as "Type"
FROM sysobjects o
INNER JOIN sysobjects od ON od.id = o.id
LEFT OUTER JOIN syscolumns c ON o.id = c.id AND o.type = 'P'
LEFT OUTER JOIN master.dbo.spt_provider_types d ON c.xtype = d.ss_dtype
WHERE c.length = case
when d.fixlen > 0 then d.fixlen
else c.length
end
AND o.name = @SPName
OPEN cursor_Params
FETCH NEXT FROM cursor_Params INTO @ParamName,@ParamDataType,@ParamType
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Populated = 0
BEGIN
PRINT @strHTML
END
SET @Populated = 1
PRINT '
<TR BGCOLOR="#FFFFFF">
<TD WIDTH="20%">' + ISNULL(convert(varchar(200), @ParamType), ' ') + ' </TD>
<TD WIDTH="40%">' + ISNULL(convert(varchar(200), @ParamName), ' ') + ' </TD>
<TD WIDTH="40%">' + ISNULL(convert(varchar(200), @ParamDataType), ' ') + ' </TD>
</TR>
'
FETCH NEXT FROM cursor_Params INTO @ParamName,@ParamDataType,@ParamType
END
CLOSE cursor_Params
DEALLOCATE cursor_Params
IF @Populated = 1
BEGIN
PRINT '
</TABLE>
</DIV>
'
END
PRINT '
</TD>
</TR>
<TR BGCOLOR="EEEEEE">
<TD CLASS="Sub" WIDTH="20%"><B>Table Dependencies</B> </TD>
<TD CLASS="Sub" WIDTH="25%"><B>Column Dependencies</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Column Type</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Size</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Precision</B> </TD>
<TD CLASS="Sub" WIDTH="05%"><B>Scale</B> </TD>
<TD CLASS="Sub" WIDTH="20%"><B>Collation</B> </TD>
</TR>
'
DECLARE cursor_spdeps CURSOR FOR
SELECT TableSysObjects.name AS [Table],
col.name AS [Column],
(SELECT TOP 1 systypes.[name] FROM systypes WHERE xtype = col.xtype),
col.length,
col.prec,
col.scale,
col.[collation]
FROM sysobjects ViewSysObjects
LEFT OUTER JOIN sysdepends dep ON ViewSysObjects.id = dep.id
LEFT OUTER JOIN sysobjects TableSysObjects ON dep.depid = TableSysObjects.id
LEFT OUTER JOIN syscolumns col ON dep.depnumber = col.colid AND TableSysObjects.id = col.id
WHERE ViewSysObjects.xtype = 'P'
And ViewSysObjects.category = 0
AND ViewSysObjects.name = @SPName
ORDER BY ViewSysObjects.name,TableSysObjects.name,col.name
OPEN cursor_spdeps
FETCH NEXT FROM cursor_spdeps INTO @SPTableDep,@SPColDep,@SPColDepType,@SPColDepLength,@SPColDepPrec,@SPColDepScale,@SPColDepCollation
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @SPColDep = ''
BEGIN
SET @SPColDep = ' '
END
PRINT '
<TR>
<TD>' + ISNULL(convert(varchar(200), @SPTableDep), ' ') + ' </TD>
<TD>' + ISNULL(convert(varchar(200), @SPColDep), ' ') + ' </TD>
<TD>' + ISNULL(@SPColDepType, ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @SPColDepLength), ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @SPColDepPrec), ' ') + ' </TD>
<TD CLASS="Num">' + ISNULL(convert(varchar(5), @SPColDepScale), ' ') + ' </TD>
<TD>' + ISNULL(@SPColDepCollation, ' ') + ' </TD>
</TR>
'
FETCH NEXT FROM cursor_spdeps INTO @SPTableDep,@SPColDep,@SPColDepType,@SPColDepLength,@SPColDepPrec,@SPColDepScale,@SPColDepCollation
END
CLOSE cursor_spdeps
DEALLOCATE cursor_spdeps
PRINT '
</TABLE>
</DIV>
<p><A CLASS="Index" HREF="#_top">Back To Top ^</A></p>
'
FETCH NEXT FROM cursor_sp INTO @SPName
END
CLOSE cursor_sp
DEALLOCATE cursor_sp
--
-- complete
--
PRINT '
</BODY>
</HTML>
'
[/script]
[tabledef 1]
Create Table [System_MainTable] (
[SystemMainKey] INTEGER IDENTITY(1,1)
CONSTRAINT [System_MainTable_Primary_Key] PRIMARY KEY,
[MachineName] VARCHAR(50)
CONSTRAINT [System_MainTable_MachineName_Key] UNIQUE,
[PhysicalMemory] INTEGER,
[MemoryCapacity] INTEGER,
[RAMSlots] INTEGER,
[ChassisType] VARCHAR(50),
[CPUType] VARCHAR(255),
[CPUTypeExtended] VARCHAR(255),
[MHz] INTEGER,
[CPUsLogical] INTEGER,
[CPUsPhysical] INTEGER,
[VideoAdapter] VARCHAR(255),
[VideoMemorySize] INTEGER,
[VideoResolution] VARCHAR(50),
[VideoRefresh] SMALLINT,
[AudioDevice] VARCHAR(255),
[MonitorMfg] VARCHAR(255),
[MonitorModel] VARCHAR(255),
[MonitorType] VARCHAR(50),
[MonitorRatio] VARCHAR(50),
[MonitorMaximumResolution] VARCHAR(50),
[MonitorSerialNumber] VARCHAR(50),
[MonitorManufactureDate] SMALLDATETIME,
[MonitorSize] INTEGER,
[ComputerType] VARCHAR(255),
[SerialNumber] VARCHAR(255),
[UUID] VARCHAR(255),
[BIOSDescription] VARCHAR(255),
[BIOSDate] SMALLDATETIME,
[SystemMfg] VARCHAR(255),
[SystemModel] VARCHAR(255),
[MotherboardMfg] VARCHAR(255),
[MotherboardModel] VARCHAR(255),
[ScanStatus] VARCHAR(255),
[LastScanned] DATETIME,
[LastScannedDuration] VARCHAR(50),
[LastScannedSuccess] DATETIME,
[LastScannedSuccessDuration] VARCHAR(50)
);
[/tabledef 1]
[tabledef 2]
Create Table [OperatingSystem] (
[MachineName] VARCHAR(50)
CONSTRAINT [OperatingSystem_Primary_Key] PRIMARY KEY
CONSTRAINT [OperatingSystem_MachineName_FKey] FOREIGN KEY
REFERENCES [System_MainTable] ([MachineName])
ON DELETE CASCADE ON UPDATE CASCADE,
[OperatingSystem] VARCHAR(255),
[ServicePack] VARCHAR(50),
[OSType] VARCHAR(50),
[OSTypeWMI] VARCHAR(255),
[WindowsPath] VARCHAR(50),
[WindowsProductKey] VARCHAR(50),
[WindowsProductID] VARCHAR(50),
[ClockReading] SMALLDATETIME,
[WindowsInstallDate] SMALLDATETIME,
[BootTime] SMALLDATETIME,
[Uptime] VARCHAR(50),
[ScreenSaver] VARCHAR(50),
[ScreenSaverTimeout] INTEGER,
[ScreenSaverSecure] VARCHAR(50),
[TimeZone] VARCHAR(50),
[DaylightSavings] VARCHAR(50),
[RegisteredOwner] VARCHAR(50),
[RegisteredOrganization] VARCHAR(50),
[ClockVariance] VARCHAR(50),
[AutomaticUpdateStatus] VARCHAR(255),
[AutomaticUpdateOptions] VARCHAR(255),
[MachineSID] VARCHAR(255)
);
[/tabledef 2]
[tabledef 3]
Create Table [Software] (
[SoftwareKey] INTEGER IDENTITY(1,1)
CONSTRAINT [Software_Primary_Key] PRIMARY KEY,
[SystemMainKey] INTEGER
CONSTRAINT [Software_SystemMainKey_FKey] FOREIGN KEY
REFERENCES [System_MainTable] ([SystemMainKey])
ON DELETE CASCADE ON UPDATE CASCADE,
[Software] VARCHAR(255),
[Version] VARCHAR(255),
[Publisher] VARCHAR(255),
[InstallDate] SMALLDATETIME,
[ProductID] VARCHAR(50),
[ProductKey] VARCHAR(50),
[RegisteredCompany] VARCHAR(255),
[RegisteredOwner] VARCHAR(255),
[UninstallPath] NTEXT,
[InstallPath] NTEXT,
[UseCount] INTEGER,
[FrequencyUsed] VARCHAR(50),
[Size] INTEGER,
[LastUsed] SMALLDATETIME
);
Create Index [Software_SystemMainKey_Key]
ON [Software] (
[SystemMainKey] ASC
);
[/tabledef 3]
Thanks,— Dave
-
This was removed by the editor as SPAM
-
Haven't decoded it as it's a bit long
If you know that is the weak section, I'd break apart the query from the union and then remove a table at a time, searching for where the query seems to be breaking. Once you understand how it is processing, then you can probably fix it.
-
The code highlighted in red is trying to put round, square, triangle, and oval pegs into a square hole. My advice is to scrap this section of code, and handle primary keys, foreign keys, check constraints, defaults, and indexes in separate sections of code, each producing its own separate section of documentation. For examples of how to handle each section, refer to the sp_help, sp_helpindex, and sp_helpconstraint system stored procedures in the master database. Also, check out: http://www.sqlsavior.com/describe.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply