需要：读取库里面所有 表、以及表描述、 列、列描述
create PROCEDURE [dbo].[ProGetAllTableName] AS BEGIN --开启事物 BEGIN TRAN tran_insert ----删除表 (不做判断 特殊处理) DELETE from _Columns; DELETE from _Tables; INSERT INTO [_Tables] Select NEWID(), --Id so.name AS tableName, --TbName CONVERT(Varchar(50),ISNULL((SELECT value FROM sys.extended_properties where major_id=so.id AND minor_id=0),so.name) ) AS tableNameDetails--TbDetails FROM SysObjects so Where (so.XType='U' OR so.XType='V') AND( CHARINDEX('_',so.name)!=1 ) ; INSERT INTO [dbo].[_Columns] SELECT NEWID(), --Id a.name AS FieldName , --ColName CONVERT(Varchar(50),isnull(g.[value],a.name) ) AS FieldDesc ,--ColDetails d.NAME AS TabName --TbName FROM syscolumns a inner join sysobjects d on a.id=d.id and (d.xtype='U' or d.xtype='V') and d.name<>'dtproperties' and charindex('_',d.name)<1 left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id order by d.NAME, a.colorder --结束事物 COMMIT TRAN tran_insert END
ps:
SELECT d.NAME AS TabName ,isnull(f.value,' ') TabDesc ,a.colorder AS FieldIdx ,a.name AS FieldName , (case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then 1 else 0 END ) AS IsIdentity ,ispk=case when ic.index_id is not null then 1 else 0 end , b.name AS FieldType,COLUMNPROPERTY(a.id,a.name,'PRECISION') AS FieldLength, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS Scale,(case when a.isnullable=1 then 1 ELSE 0 END ) AS isAllowNull, isnull(e.text,'') AS FieldDefaultValue, isnull(g.[value],' ') AS FieldDesc FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and (d.xtype='U' or d.xtype='V') and d.name<>'dtproperties' and charindex('_',d.name)<1 left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 left join sys.indexes ix on d.id=ix.object_id and ix.is_primary_key=1 left join sys.index_columns ic on ix.object_id=ic.object_id and a.colid=ic.column_id and ix.index_id=ic.index_id order by d.NAME
--后续 ：记录sql管理维护
场景1：查看所有的表名称(很多时候 不知道表，也不知道表叫什么名称，只记得备注)。
vw_tableDes
create view [dbo].[vw_TableDes] as select TableCode=object_name(major_id) ,TableName=cast([value] as nvarchar(100)) from sys.extended_properties where minor_id=0
要求在建表的时候 需要维护扩展属性：
场景2：有查看所有表，就有查看字段
这里以GGUser为测试 (只备注了name)
create procedure [dbo].[up_Help] @objname nvarchar(776) = NULL -- object name we're after as -- PRELIMINARY set nocount on SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @dbname sysname ,@no varchar(35), @yes varchar(35), @none varchar(35) select @no = 'no', @yes = 'yes', @none = 'none' -- If no @objname given, give a little info about all objects. if @objname is null begin -- DISPLAY ALL SYSOBJECTS -- if (serverproperty('EngineEdition') != 5) begin select 'Name' = o.name, 'Desc' = e.value, 'Owner' = user_name(ObjectProperty( object_id, 'ownerid')), 'Object_type' = substring(v.name,5,31) from sys.all_objects o inner join master.dbo.spt_values v on o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T' left join sys.extended_properties e on o.object_id=e.major_id and e.minor_id=0 order by [Owner] asc, Object_type desc, Name asc end else begin select 'Name' = o.name, 'Desc' = e.value, 'Owner' = user_name(ObjectProperty( object_id, 'ownerid')), 'Object_type' = substring(v.name,5,31) from sys.all_objects o inner join sys.spt_values v on o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T' left join sys.extended_properties e on o.object_id=e.major_id and e.minor_id=0 order by [Owner] asc, Object_type desc, Name asc end print ' ' -- DISPLAY ALL USER TYPES select 'User_type' = name, 'Storage_type' = type_name(system_type_id), 'Length' = max_length, --'Prec' = Convert(int,TypePropertyEx(user_type_id, 'precision')), --'Scale' = Convert(int,TypePropertyEx(user_type_id, 'scale')), 'Nullable' = case when is_nullable = 1 then @yes else @no end, 'Default_name' = isnull(object_name(default_object_id), @none), 'Rule_name' = isnull(object_name(rule_object_id), @none), 'Collation' = collation_name from sys.types where user_type_id > 256 order by name return(0) end -- Make sure the @objname is local to the current database. select @dbname = parsename(@objname,3) if @dbname is null select @dbname = db_name() else if @dbname <> db_name() begin raiserror(15250,-1,-1) return(1) end -- @objname must be either sysobjects or systypes: first look in sysobjects declare @objid int declare @sysobj_type char(2) select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname) -- IF NOT IN SYSOBJECTS, TRY SYSTYPES -- if @objid is null begin -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME select @objid = type_id(@objname) -- IF NOT IN SYSTYPES, GIVE UP if @objid is null begin raiserror(15009,-1,-1,@objname,@dbname) return(1) end -- DATA TYPE HELP (prec/scale only valid for numerics) select 'Type_name' = name, 'Storage_type' = type_name(system_type_id), 'Length' = max_length, --'Prec' = Convert(int,TypePropertyEx(user_type_id, 'precision')), --'Scale' = Convert(int,TypePropertyEx(user_type_id, 'scale')), 'Nullable' = case when is_nullable=1 then @yes else @no end, 'Default_name' = isnull(object_name(default_object_id), @none), 'Rule_name' = isnull(object_name(rule_object_id), @none), 'Collation' = collation_name from sys.types where user_type_id = @objid return(0) end -- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO if (serverproperty('EngineEdition') != 5) begin select 'Name' = o.name, 'Desc' = (select top 1 value from sys.extended_properties where major_id=@objid and minor_id=0), 'Owner' = user_name(ObjectProperty( object_id, 'ownerid')), 'Type' = substring(v.name,5,31), 'Created_datetime' = o.create_date from sys.all_objects o, master.dbo.spt_values v where o.object_id = @objid and o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T' end else begin select 'Name' = o.name, 'Desc' = (select top 1 value from sys.extended_properties where major_id=@objid and minor_id=0), 'Owner' = user_name(ObjectProperty( object_id, 'ownerid')), 'Type' = substring(v.name,5,31), 'Created_datetime' = o.create_date from sys.all_objects o, sys.spt_values v where o.object_id = @objid and o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T' end print ' ' -- DISPLAY COLUMN IF TABLE / VIEW if exists (select * from sys.all_columns where object_id = @objid) begin -- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE -- There must be a ',' immediately after each type name (including last one), -- because that's what we'll search for in charindex later. declare @precscaletypes nvarchar(150) select @precscaletypes = N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,' -- INFO FOR EACH COLUMN print ' ' select 'Column_name' = cl.name, 'Column_Desc' = ep.value, 'Type' = type_name(user_type_id), 'Computed' = case when ColumnProperty(object_id, cl.name, 'IsComputed') = 0 then @no else @yes end, 'Length' = convert(int, max_length), -- for prec/scale, only show for those types that have valid precision/scale -- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset' 'Prec' = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0 then convert(char(5),ColumnProperty(object_id, cl.name, 'precision')) else ' ' end, 'Scale' = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0 then convert(char(5),OdbcScale(system_type_id,scale)) else ' ' end, 'Nullable' = case when is_nullable = 0 then @no else @yes end, 'TrimTrailingBlanks' = case ColumnProperty(object_id, cl.name, 'UsesAnsiTrim') when 1 then @no when 0 then @yes else '(n/a)' end, 'FixedLenNullInSource' = case when type_name(system_type_id) not in ('varbinary','varchar','binary','char') then '(n/a)' when is_nullable = 0 then @no else @yes end, 'Collation' = collation_name from sys.all_columns cl left join sys.extended_properties ep on cl.object_id=ep.major_id and ep.minor_id=cl.column_id where object_id = @objid order by cl.column_id --select * from sys.all_columns -- IDENTITY COLUMN? --if @sysobj_type in ('S ','U ','V ','TF') --begin -- print ' ' -- declare @colname sysname -- select @colname = col_name(@objid, column_id) from sys.identity_columns where object_id = @objid -- select -- 'Identity' = isnull(@colname,'No identity column defined.'), -- 'Seed' = ident_seed(@objname), -- 'Increment' = ident_incr(@objname), -- 'Not For Replication' = ColumnProperty(@objid, @colname, 'IsIDNotForRepl') -- -- ROWGUIDCOL? -- print ' ' -- select @colname = null -- select @colname = name from sys.columns where object_id = @objid and is_rowguidcol = 1 -- select 'RowGuidCol' = isnull(@colname,'No rowguidcol column defined.') --end end EXEC sys.sp_helpindex @objname -- DISPLAY ANY PARAMS --if exists (select * from sys.all_parameters where object_id = @objid) --begin -- -- INFO ON PROC PARAMS -- print ' ' -- select -- 'Parameter_name' = name, -- 'Type' = type_name(user_type_id), -- 'Length' = max_length, -- 'Prec' = case when type_name(system_type_id) = 'uniqueidentifier' then precision -- else OdbcPrec(system_type_id, max_length, precision) end, -- 'Scale' = OdbcScale(system_type_id, scale), -- 'Param_order' = parameter_id, -- 'Collation' = convert(sysname, case when system_type_id in (35, 99, 167, 175, 231, 239) -- then ServerProperty('collation') end) -- from sys.all_parameters where object_id = @objid --end -- DISPLAY TABLE INDEXES & CONSTRAINTS --if @sysobj_type in ('S ','U ') --begin -- print ' ' -- EXEC sys.sp_objectfilegroup @objid -- print ' ' -- EXEC sys.sp_helpindex @objname -- print ' ' -- EXEC sys.sp_helpconstraint @objname,'nomsg' -- if (select count(*) from sysdepends where depid = @objid and deptype = 1) = 0 -- begin -- raiserror(15647,-1,-1,@objname) -- No views with schemabinding reference table '%ls'. -- end -- else -- begin -- select distinct 'Table is referenced by views' = obj.name from sys.objects obj, sysdepends deps -- where obj.type ='V' and obj.object_id = deps.id and deps.depid = @objid -- and deps.deptype = 1 group by obj.name -- end --end --else if @sysobj_type in ('V ') --begin -- -- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID -- print ' ' -- raiserror(15469,-1,-1,@objname) -- No constraints defined -- print ' ' -- raiserror(15470,-1,-1,@objname) -- No foreign keys reference table '%ls'. -- EXEC sys.sp_helpindex @objname --end return (0) -- sp_help
建表工具 一般使用PowerDesigner 当然直接在sql 里面也没问题。
-附加sql 建表 文件 父子结构 https://www.cnblogs.com/kissdodog/p/3297894.html
--demo
CREATE TABLE [dbo].[FeFileLIst]( [Id] [uniqueidentifier] NOT NULL, [FDBId] [int] NOT NULL, [AccId] [smallint] NOT NULL, [ClassCode] [varchar](50) NOT NULL, [FileCode] [varchar](50) NOT NULL, [FileName] [nvarchar](200) NOT NULL, [ExteName] [varchar](20) NULL, [FileSize] [decimal](18, 2) NULL, [FilePath] [nvarchar](1000) NOT NULL, [Creator] [nvarchar](50) NULL, [CreatorCode] [varchar](50) NULL, [CreateDate] [datetime] NULL, [Updater] [nvarchar](50) NULL, [UpdaterCode] [varchar](50) NULL, [UpdateDate] [datetime] NULL, [IsDelete] [tinyint] NOT NULL, [OperDept] [nvarchar](20) NULL, [RoCode] [varchar](50) NULL, CONSTRAINT [PK_FeFileLIst] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[FeFileLIst] ADD DEFAULT (newsequentialid()) FOR [Id] GO ALTER TABLE [dbo].[FeFileLIst] ADD DEFAULT ((1)) FOR [FDBId] GO ALTER TABLE [dbo].[FeFileLIst] ADD DEFAULT ((0)) FOR [IsDelete] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'Id' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'帐套Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'FDBId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'AccId' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'AccId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分类编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'ClassCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'FileCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'FileName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'扩展名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'ExteName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件大小(M)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'FileSize' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'FilePath' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'Creator' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'CreatorCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'CreateDate' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'Updater' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新人编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'UpdaterCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'UpdateDate' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'删除标记' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'IsDelete' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'操作部门' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'OperDept' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst', @level2type=N'COLUMN',@level2name=N'RoCode' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件管理列表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FeFileLIst' GO
ALTER view [dbo].[v_LkpFileManage] as WITH COMMENT_CTE(id,pId,name,tLevel,isParen,Codetypes,keyIdExtend,Paths)AS( /*基本语句*/ SELECT t.ClassCode as id, t.ParentCode as pId ,t.ClassName as name, 0 AS tLevel ,isParen=CAST( 'true' as nvarchar(10)) ,t.GroupCode as Codetypes ,t.id as keyIdExtend ,Paths=cast(t.ClassName as nvarchar(2000)) FROM LkpFileManage t WHERE ParentCode ='' and IsDelete=0 and IsEnable=1 UNION ALL /*递归语句*/ SELECT t.ClassCode as id, t.ParentCode as pId ,t.ClassName as name,ce.tLevel + 1 ,isParen=CAST( 'fasle' as nvarchar(10)) ,t.GroupCode as Codetypes,t.Id ,Paths=cast(ce.Paths+'->'+t.ClassName as nvarchar(2000)) FROM LkpFileManage t INNER JOIN COMMENT_CTE AS ce ON t.ParentCode = ce.id where IsEnable=1 ) SELECT * FROM COMMENT_CTE GO
