SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO -- ============================================= -- Description: <Éú³ÉÊý¾Ý¿â×Öµä> -- ============================================= SET NOCOUNT ON; /* *Êä³öÍ·²¿ÐÅÏ¢ */ PRINT ''; PRINT ''; PRINT ' '; PRINT ' Êý¾Ý¿â×Öµä'; PRINT ' '; PRINT ' '; PRINT ' '; PRINT '

' + DB_NAME() + ' Êý¾Ý¿â×Öµä


Éú³ÉÓÚ£º' + CONVERT(VARCHAR, GETDATE(), 120) + '

'; /* *Êý¾Ý¿â²Ëµ¥Áбí */ --Êý¾Ý¿â±í 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 '
Ŀ¼£º
    '; WHILE @i_t1 <= @count_t1 BEGIN SELECT @tname_cur = tableName FROM @tableDBTABLE WHERE id = @i_t1; PRINT '
  1. 񡜧' + @tname_cur + '
    2. '; SET @i_t1 = @i_t1 + 1; END; PRINT '
'; 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 '
'; PRINT ''; PRINT '

' + CAST(@i_t1 AS VARCHAR(10)) + '.' + @tname_cur + ' ±í ' + @tabledesc + '

'; PRINT ''; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; PRINT ' '; DECLARE TRows CURSOR FOR SELECT ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' 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 ' '; PRINT ' '; PRINT @×Ö¶ÎÃû³Æ; PRINT @ÀàÐÍ; PRINT @³¤¶È; PRINT @ÊýÖµ¾«¶È; PRINT @СÊýλÊý; PRINT @ĬÈÏÖµ; PRINT @ÔÊÐíΪ¿Õ; PRINT @Íâ¼ü; PRINT @Ö÷¼ü; PRINT @ÃèÊö; PRINT ' '; FETCH NEXT FROM TRows INTO @×Ö¶ÎÃû³Æ, @ÀàÐÍ, @³¤¶È, @ÊýÖµ¾«¶È, @СÊýλÊý, @ĬÈÏÖµ, @ÔÊÐíΪ¿Õ, @Íâ¼ü, @Ö÷¼ü, @ÃèÊö; SET @i_t2 = @i_t2 + 1; END; CLOSE TRows; DEALLOCATE TRows; PRINT ''; PRINT '
ÐòºÅ×Ö¶ÎÃû³ÆÀàÐͳ¤¶ÈÊýÖµ¾«¶ÈСÊýλÊýĬÈÏÖµÔÊÐíΪ¿ÕÍâ¼üÖ÷¼üÃèÊö
' + CAST(clmns.name AS VARCHAR(35)) + '' + CAST(udt.name AS CHAR(15)) + '' + 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)) + '' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '' + ISNULL(CAST(cnstr.definition AS VARCHAR(20)), '') + '' + ( CASE WHEN clmns.is_nullable = 1 THEN '¡Ì' ELSE '' END ) + '' + ( CASE WHEN clmns.is_computed = 1 THEN '¡Ì' ELSE '' END ) + '' + ( CASE WHEN clmns.is_identity = 1 THEN '¡Ì' ELSE '' END ) + '' + ISNULL(CAST(exprop.value AS VARCHAR(500)), '') + '
' + CAST(@i_t2 AS VARCHAR(10)) + '
TOP¡ü
'; PRINT '
'; SET @i_t1 = @i_t1 + 1; END; END; PRINT ' '; PRINT ''; SET NOCOUNT OFF;