sql读取 所有表、结构 描述

需要：读取库里面所有 表、以及表描述、 列、列描述

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