SET ANSI_NULLS ON;
|
GO
|
SET QUOTED_IDENTIFIER ON;
|
GO
|
-- =============================================
|
-- Description: <Éú³ÉÊý¾Ý¿â×Öµä>
|
-- =============================================
|
|
SET NOCOUNT ON;
|
/*
|
*Êä³öÍ·²¿ÐÅÏ¢
|
*/
|
PRINT '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">';
|
PRINT '<html xmlns="http://www.w3.org/1999/xhtml">';
|
PRINT ' <head>';
|
PRINT ' <title>Êý¾Ý¿â×Öµä</title>';
|
PRINT ' <style type="text/css">';
|
PRINT ' body{margin:0;cursor:default;font-family: Arial, Helvetica, sans-serif, "΢ÈíÑźÚ" , "ËÎÌå";}';
|
PRINT ' .tableBox{margin:10px auto; padding:0px; width:1200px; height:auto; background:#EAEFF3; border:1px solid #45360A; margin-top:50px;}';
|
PRINT ' .tableBox h3 {font-size:12pt; height:30px; line-height:30px; background:#460B6A; padding:0px 0px 0px 15px; color:#FFF; margin:0px; text-align:left }';
|
PRINT ' .tableBox table {width:1200px; padding:0px }';
|
PRINT ' .tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#C0DBF7; font-size:14px; }';
|
PRINT ' .tableBox td {font-size: 12px;height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; }';
|
PRINT ' .tableBox tr:hover { background:#C0DBF7; } ';
|
PRINT ' .tableBox .field{font-weight:bold;color:#276926;}';
|
PRINT ' </style>';
|
PRINT ' </head>';
|
PRINT ' <body>';
|
PRINT '<div style="text-align:center;"><h2>' + DB_NAME()
|
+ ' Êý¾Ý¿â×Öµä</h2></br><font style="color:gray;">Éú³ÉÓÚ£º'
|
+ CONVERT(VARCHAR, GETDATE(), 120) + '</font></div></br>';
|
|
/*
|
*Êý¾Ý¿â²Ëµ¥Áбí
|
*/
|
--Êý¾Ý¿â±í
|
DECLARE @tableDBTABLE TABLE
|
(
|
id INT IDENTITY(1, 1)
|
PRIMARY KEY ,
|
tableName NVARCHAR(300)
|
);
|
|
|
INSERT INTO @tableDBTABLE
|
( tableName
|
)
|
SELECT DISTINCT
|
TABLE_NAME AS tableName
|
FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE ( SELECT COUNT(*)
|
FROM sys.tables AS tbl
|
INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id
|
LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id
|
AND 1 = idx.is_primary_key
|
LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id
|
AND idxcol.column_id = clmns.column_id
|
AND idxcol.object_id = clmns.object_id
|
AND 0 = idxcol.is_included_column
|
LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id
|
LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id
|
AND typ.user_type_id = typ.system_type_id
|
LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id = clmns.default_object_id
|
LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id
|
AND exprop.minor_id = clmns.column_id
|
AND exprop.name = 'MS_Description'
|
WHERE ( tbl.name = TABLE_NAME
|
-- AND exprop.class = 1
|
)
|
) > 0
|
ORDER BY TABLE_NAME;
|
|
--Êä³ö±íĿ¼ÐÅÏ¢
|
DECLARE @tname_cur VARCHAR(200);
|
DECLARE @count_t1 INT;
|
DECLARE @i_t1 INT;
|
DECLARE @i_t2 INT;
|
|
DECLARE @×Ö¶ÎÃû³Æ VARCHAR(200);
|
DECLARE @ÀàÐÍ VARCHAR(200);
|
DECLARE @³¤¶È VARCHAR(200);
|
DECLARE @ÊýÖµ¾«¶È VARCHAR(200);
|
DECLARE @СÊýλÊý VARCHAR(200);
|
DECLARE @ĬÈÏÖµ VARCHAR(200);
|
DECLARE @ÔÊÐíΪ¿Õ VARCHAR(200);
|
DECLARE @Íâ¼ü VARCHAR(200);
|
DECLARE @Ö÷¼ü VARCHAR(200);
|
DECLARE @ÃèÊö VARCHAR(4000);
|
|
--³õʼ»¯Öµ
|
SET @i_t1 = 1;
|
SET @i_t2 = 1;
|
SELECT @count_t1 = COUNT(*)
|
FROM @tableDBTABLE;
|
|
|
IF @count_t1 > 0
|
BEGIN
|
PRINT '<div style="text-align:left; margin:20px 0px 50px 50px;"> Ŀ¼£º<br><ol>';
|
WHILE @i_t1 <= @count_t1
|
BEGIN
|
SELECT @tname_cur = tableName
|
FROM @tableDBTABLE
|
WHERE id = @i_t1;
|
PRINT '<li><a href="#' + @tname_cur + '">񡜧' + @tname_cur
|
+ '</a></li>';
|
SET @i_t1 = @i_t1 + 1;
|
END;
|
PRINT '</ol></div>';
|
END;
|
--Êä³ö±íÐÐÐÅÏ¢
|
IF @count_t1 > 0
|
BEGIN
|
SET @i_t1 = 1;
|
WHILE @i_t1 <= @count_t1
|
BEGIN
|
SELECT @tname_cur = tableName
|
FROM @tableDBTABLE
|
WHERE id = @i_t1;
|
|
DECLARE @tabledesc NVARCHAR(4000);--±íÃèÊö
|
SELECT @tabledesc = CAST(value AS VARCHAR(4000))
|
FROM sys.extended_properties AS A
|
WHERE A.major_id = OBJECT_ID(@tname_cur)
|
AND name = 'MS_Description'
|
--AND minor_id = 0;
|
SET @tabledesc = CASE WHEN ( @tabledesc IS NULL
|
OR LEN(RTRIM(LTRIM(@tabledesc))) <= 0
|
) THEN ' '
|
ELSE ' : ' + @tabledesc
|
END;
|
|
--Êä³ö±íÍ·²¿ÐÅÏ¢
|
PRINT ' <div class="tableBox">';
|
PRINT '<a name="' + @tname_cur + '"></a>';
|
PRINT ' <h3>' + CAST(@i_t1 AS VARCHAR(10)) + '.' + @tname_cur
|
+ ' ±í ' + @tabledesc + '</h3>';
|
PRINT '';
|
|
PRINT ' <table cellspacing="0">';
|
PRINT ' <tr>';
|
PRINT ' <th>ÐòºÅ</th>';
|
PRINT ' <th>×Ö¶ÎÃû³Æ</th>';
|
PRINT ' <th>ÀàÐÍ</th>';
|
PRINT ' <th>³¤¶È</th>';
|
PRINT ' <th>ÊýÖµ¾«¶È</th>';
|
PRINT ' <th>СÊýλÊý</th>';
|
PRINT ' <th>ĬÈÏÖµ</th>';
|
PRINT ' <th>ÔÊÐíΪ¿Õ</th>';
|
PRINT ' <th>Íâ¼ü</th>';
|
PRINT ' <th>Ö÷¼ü</th>';
|
PRINT ' <th>ÃèÊö</th>';
|
PRINT ' </tr>';
|
|
DECLARE TRows CURSOR
|
FOR
|
SELECT ' <td class="field">'
|
+ CAST(clmns.name AS VARCHAR(35)) + '</td>' ,
|
' <td>' + CAST(udt.name AS CHAR(15)) + '</td>' ,
|
' <td>'
|
+ CAST(CAST(CASE WHEN typ.name IN ( N'nchar',
|
N'nvarchar' )
|
AND clmns.max_length <> -1
|
THEN clmns.max_length / 2
|
ELSE clmns.max_length
|
END AS INT) AS VARCHAR(20)) + '</td>' ,
|
' <td>'
|
+ CAST(CAST(clmns.precision AS INT) AS VARCHAR(20))
|
+ '</td>' ,
|
' <td>'
|
+ CAST(CAST(clmns.scale AS INT) AS VARCHAR(20))
|
+ '</td>' ,
|
' <td>'
|
+ ISNULL(CAST(cnstr.definition AS VARCHAR(20)), '')
|
+ '</td>' ,
|
' <td>'
|
+ ( CASE WHEN clmns.is_nullable = 1 THEN '¡Ì'
|
ELSE ''
|
END ) + '</td>' ,
|
' <td>'
|
+ ( CASE WHEN clmns.is_computed = 1 THEN '¡Ì'
|
ELSE ''
|
END ) + '</td>' ,
|
' <td>'
|
+ ( CASE WHEN clmns.is_identity = 1 THEN '¡Ì'
|
ELSE ''
|
END ) + '</td>' ,
|
' <td style="">'
|
+ ISNULL(CAST(exprop.value AS VARCHAR(500)), '')
|
+ '</td>'
|
FROM sys.tables AS tbl
|
INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id
|
LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id
|
AND 1 = idx.is_primary_key
|
LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id
|
AND idxcol.column_id = clmns.column_id
|
AND idxcol.object_id = clmns.object_id
|
AND 0 = idxcol.is_included_column
|
LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id
|
LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id
|
AND typ.user_type_id = typ.system_type_id
|
LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id = clmns.default_object_id
|
LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id
|
AND exprop.minor_id = clmns.column_id
|
AND exprop.name = 'MS_Description'
|
WHERE ( tbl.name = @tname_cur
|
--AND exprop.class = 1
|
)
|
ORDER BY clmns.column_id ASC;
|
|
SET @i_t2 = 1;
|
OPEN TRows;
|
FETCH NEXT FROM TRows INTO @×Ö¶ÎÃû³Æ, @ÀàÐÍ, @³¤¶È, @ÊýÖµ¾«¶È, @СÊýλÊý, @ĬÈÏÖµ,
|
@ÔÊÐíΪ¿Õ, @Íâ¼ü, @Ö÷¼ü, @ÃèÊö;
|
|
--Êä³ö±íÐÐÊý¾Ý
|
WHILE @@FETCH_STATUS = 0
|
BEGIN
|
PRINT ' <tr>';
|
PRINT ' <td>' + CAST(@i_t2 AS VARCHAR(10)) + '</td>';
|
PRINT @×Ö¶ÎÃû³Æ;
|
PRINT @ÀàÐÍ;
|
PRINT @³¤¶È;
|
PRINT @ÊýÖµ¾«¶È;
|
PRINT @СÊýλÊý;
|
PRINT @ĬÈÏÖµ;
|
PRINT @ÔÊÐíΪ¿Õ;
|
PRINT @Íâ¼ü;
|
PRINT @Ö÷¼ü;
|
PRINT @ÃèÊö;
|
PRINT ' </tr>';
|
FETCH NEXT FROM TRows INTO @×Ö¶ÎÃû³Æ, @ÀàÐÍ, @³¤¶È, @ÊýÖµ¾«¶È,
|
@СÊýλÊý, @ĬÈÏÖµ, @ÔÊÐíΪ¿Õ, @Íâ¼ü, @Ö÷¼ü, @ÃèÊö;
|
SET @i_t2 = @i_t2 + 1;
|
END;
|
CLOSE TRows;
|
DEALLOCATE TRows;
|
PRINT '<tr><td colspan="11" style="text-align:right"><a href="#top">TOP¡ü</a></td></tr>';
|
PRINT ' </table>';
|
PRINT ' </div>';
|
|
SET @i_t1 = @i_t1 + 1;
|
END;
|
END;
|
PRINT ' </body>';
|
PRINT '</html>';
|
SET NOCOUNT OFF;
|
