System development, data dictionary table update
Do you find that in the system development process, your database is constantly changing, initially issued a data sheet definition of each developer, has become unrecognizable, if you have developed by this project, the following people Teacher said, how to change it again, not to inform us, sometimes it is busy, and it is not necessarily notified to all developers. To this end, I specially made an automated tool. Whenever the data table changes, you don't need to modify the original data dictionary document again. You just do it according to my steps, guarantee that all within 2 minutes. , Help you get the update of the data dictionary document. Note the method, only for MS SQL Server 2000. Unfortunately, all this has not been fully automated, who has this interest, perfect it, I believe that this will get it within 1 minute. 1. In your system database, create the following stored procedure This stored procedure is to get data dictionary information
create Procedure GetAllTableInfo As begin DECLARE @au_id varchar (11), @strName varchar (50), @Tid int, @ strOwner varchar (50) - obtain all of the user information table DECLARE All_Tables CURSOR FOR select convert (varchar, Name), ID from sysobjects where xtype = 'u' Order by name create table #Colinfo (TName Varchar (50), - Table Name ColName Varchar (50), - Column Name Remark SQL_VARIANT, - Note Type Varchar (50), - Data Type DefaultValue VARCHAR (255), - Default ISALLOWNULL VARCHAR (10), - Whether to allow empty isprimaryKey char (2) - whether it is the primary key, use * tagged) - Primon Information Create Table #pkinfo Table_qualifier varchar (50), table_owner varchar (50), table_name varchar (50), column_name var, pk_seq int, pk_name varchar (50)) set @ strOwner = user_name () OPEN All_Tables FETCH NEXT FROM All_Tables into @ strName, @ Tid while @@ FETCH_STATUS = 0 begin - and the comment information into the table Insert Into #ColInfo SELECT @StrName, '', isnull (Value, ' '),' ',' ',' ',' From (select @strname tname) a left join :: fn_listextendedproperty ('ms_description', n'user ', n'dbo', n'table ", @strname, NULL, NULL - Table Note Information on 1 = 1 - Insert Column Information Insert Into # Colinfo Select @
Strname, C.Name CName, Convert (VARCHAR, R.VALUE) (Case C.status when 0x80 Then Char (13) CHAR (10) 'Automatic Growth Column' Else '' End), (Case WHEN " .xtype> = 34 and c.XTYPE <= 58) OR C. xipe in (61, 98, 99, 104, 127, 189) THEN T.NAME WHEN C.XTYPE IN (59, 60, 106, 108, 122) THEN T.NAME '(' Convert (Varchar , C.XPREC) ',' Convert (varchar, c.xscale) ')' Else T.Name '(' convert (varchar, c.length) ')' end), isnull (Def.Text , ''), Is' else 'No' end, '' from (Syscilumns C left syscomments def on c.cdefault = def.id and 1 = def.colid) on T.XTYPE = C.XTYPE AND T.XUSERTYPE = C.XUSERTYPE) LEFT JOIN :: Fn_ListextendedProperty ('ms_description', n'user ', n'dbo', n 'Table', @Strname, N'Column ', Null R - Column Note Information on Convert (varchar, r.objname) = Convert (varchar, c.name) where c.id = Object_ID (@strname)
insert into #PkInfo EXEC [dbo] [sp_pkeys] @ strName, @ strOwner, DEFAULT fetch next from All_Tables into @ strName, @ Tid end -. updated primary key information update #ColInfo set IsPrimaryKey = 'yes' from #PkInfo where #PkInfo .TABLE_NAME = # ColInfo.TName and # PkInfo.COLUMN_NAME = # ColInfo.ColNameClose all_Tables DEALLOCATE all_Tables select '%%%%' ObjName, '' REMARK, '' Col1, '' Col2, '' Col3, '' Col4, TName , 0 flag from #colinfo where colname = '' union select 'Name:' TNAME, Remark, '', '', ',' ', TNAME, 1 FLAG from #colinfo where colname =' union select ' Columns ',' Note ',' Data Type ',' Default ',' Whether Allowed ',' Primary Key ', TNAME, 2 from #colinfo where colname =' Union Select ColName, Remark, Type, DefaultValue, Isallownull, IsprimaryKey, TName, 3 from #colinfo where colname <> 'ORDER BY 7, 8, 3
DROP TABLE #PKINFO Drop Table #colinfo end
2. In the Query Analyzer provided by MS SQL Sever, Exec getTableInfo 3. Copy the query results information, copy it to the MS Excel Sheet 4. In the MS Excel Sheet, remove the last two columns 5. All SHEET information, copy to MS In Word, the following Word macro command SUB macro1 () '' macro1 macro 'macro in 2004-3-12 is recorded by Xu Xiajun' A: Selection.Find.clearFormatting with selection.find .text = "%%%%" .replacement .Text = "" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchByte = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With If Selection.Find.Execute Then Selection.Delete Unit: = wdCharacter, Count: = 1 Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection. Endkey unit: = wdline, extend: = WDEXTEND Selection.endkey unit: = wdline, extend: = WDEXTEND Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection.Cells.Merge Selection.MoveDown Unit: = wdLine, Count: = 1 Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection.EndKey Unit: = wdLine, Extend : = wdExtend Selection.MoveLeft Unit: = wdCharacter, Count: = 1, Extend: = wdExtend Selection.Font.Bold = wdToggle Selection.MoveRight Unit: = wdCharacter, Count: = 1 Selection.SelectCell Selection.MoveRight Unit: = wdCharacter, Count: = 4, extend: =
wdExtend Selection.Cells.Merge Selection.MoveDown Unit: = wdLine, Count: = 1 Selection.HomeKey Unit: = wdLine, Extend: = wdExtend Selection.MoveLeft Unit: = wdCharacter, Count: = 2 Selection.HomeKey Unit: = wdLine Selection .EndKey Unit: = wdLine, Extend: = wdExtend Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection.EndKey Unit: = wdLine, Extend: = wdExtend Selection.EndKey Unit: = WDLINE, EXTEND: = WDEXTEND Selection.ndKey Unit: = WDLINE, EXTEND: = WDEXTEND Selection.Font.Bold = WDToggle Selection.Movedown Unit: = WDLINE, Count: = 2 Goto a else msgbox "Modify Each is success" End if End Sub1. If you have discovered those where you do not meet your requirements, please improve your own
