sql: table,view,function, procedure created MS

--添加描述 geovindu

--https://msdn.microsoft.com/en-us/library/ms180047.aspx

--https://msdn.microsoft.com/zh-cn/library/ms180047(v=sql.120).aspx

--https://msdn.microsoft.com/zh-cn/library/ms179853(v=sql.120).aspx

--为表添加描述信息

EXECUTE sp_addextendedproperty N'MS_Description', '导入员工资料表', N'SCHEMA', N'dbo', N'table', N'StaffList', NULL, NULL

EXEC sp_updateextendedproperty N'MS_Description', '导入员工资料表', N'SCHEMA', N'dbo', N'table', N'StaffList', NULL, NULL

--为字段StaffId添加描述信息

EXECUTE sp_addextendedproperty N'MS_Description', '序号', N'SCHEMA', N'dbo', N'table', N'StaffList', N'column', N'StaffId'

EXECUTE sp_addextendedproperty N'MS_Description', '员工编号', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffNo'

EXECUTE sp_updateextendedproperty N'MS_Description', '员工编号', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffNo'

EXECUTE sp_addextendedproperty N'MS_Description', '员工姓名', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffName'

EXECUTE sp_addextendedproperty N'MS_Description', '部门名称', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffDepartmnet'

EXECUTE sp_addextendedproperty N'MS_Description', '职位名称', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffPositionName'

EXECUTE sp_addextendedproperty N'MS_Description', '生成日期', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffAddate'

--获取某一个字段的描述

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'StaffList', 'column', 'StaffName')

---穫取一個錶所有字段的描述

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'StaffList', 'column', default)

--穫取錶的描述 涂聚文

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'StaffList', default, default)

--函数注释

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'部门函数.',

@level0type = N'SCHEMA', @level0name = [dbo],

@level1type = N'FUNCTION', @level1name = f_GetDepartmentName;

GO

--函数参数注释

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'部门函数.',

@level0type = N'SCHEMA', @level0name = [dbo],

@level1type = N'FUNCTION', @level1name = f_GetDepartmentName,

@level2type = N'PARAMETER', @level2name ='@did';

GO

select * from sys.extended_properties

go

--查询函数注释

SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'FUNCTION', 'f_GetDepartmentName', default, default)

go

--函数参数注释

SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'FUNCTION', 'f_GetDepartmentName', 'PARAMETER', default)

go

---视图描述

EXECUTE sp_addextendedproperty N'MS_Description', '员工资料表', N'SCHEMA', N'dbo', N'VIEW', N'View_InventoryReport', NULL, NULL

EXEC sp_updateextendedproperty N'MS_Description', '盘点报告视图', N'SCHEMA', N'dbo', N'VIEW', N'View_InventoryReport', NULL, NULL

---

--穫取视图的描述

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'VIEW', 'View_InventoryReport', default, default)

-- --存储过程注释

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'添加作者存储过程.',

@level0type = N'SCHEMA', @level0name = [dbo],

@level1type = N'PROCEDURE', @level1name = proc_Insert_AuthorList;

GO

--存储过程参数注释

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'添加作者存储过程作者名字参数.',

@level0type = N'SCHEMA', @level0name = [dbo],

@level1type = N'PROCEDURE', @level1name = proc_Insert_AuthorList,

@level2type = N'PARAMETER', @level2name ='@AuthorName';

GO

select * from sys.extended_properties

go

--查询存储过程注释

SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'proc_Insert_AuthorList', default, default)

go

--存储过程参数注释

SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'proc_Insert_AuthorList', 'PARAMETER', default)

go

--为函数添加描述信息

EXECUTE sp_addextendedproperty N'MS_Description', '获取取回密码回答类型', N'user', N'dbo', N'function', N'fu_GetAnserTypeName', NULL, NULL

EXECUTE sp_addextendedproperty N'MS_Description', '输入ID号', N'user', N'dbo', N'function', N'fu_GetAnserTypeName', N'parameter', N'@did'

--查所有函數的描述

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'function', default, default, default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'function', 'fu_GetAnserTypeName', 'parameter', default)

--視圖描述

EXECUTE sp_addextendedproperty N'MS_Description', '公司信息视图', N'user', N'dbo', N'VIEW', N'View_CustomerList', NULL, NULL

EXECUTE sp_addextendedproperty N'MS_Description', 'ID号', N'user', N'dbo', N'view', N'View_CustomerList', N'column', N'CustomerCoNatureId'

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'view', default, default, default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'view', 'View_CustomerList', 'column', default)

--储存过程描述

EXECUTE sp_addextendedproperty N'MS_Description', '插入地址类型存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AddressTypeList', NULL, NULL

EXECUTE sp_addextendedproperty N'MS_Description', '输入地址类型名称', N'user', N'dbo', N'procedure', N'proc_Insert_AddressTypeList', N'parameter', N'@AddressTypeName'

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'procedure', default, default, default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'procedure', 'proc_Insert_AddressTypeList', 'parameter', default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'procedure', 'CreateEntrustByMRP', 'parameter', default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'APClericalCostMain', 'column', default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, default, default)

---存储过程式参数 （输入或输出）isoutparam 0:輸入 1:輸出

select * from syscolumns where ID in

  (SELECT id FROM sysobjects as a

   WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1

   and id = object_id(N'[dbo].[proc_Insert_AddressTypeListOutput]'))

--Geovin Du 20170329 涂聚文

--http://www.sqlservercentral.com/articles/Metadata/72607/
--https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

---20170329 edit: Geovin Du  涂聚文

---1.SQL查询表的所有字段的备注说明

SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,

systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,

sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as

COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns

INNER JOIN systypes

    ON syscolumns.xtype = systypes.xtype

    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id

  LEFT OUTER JOIN sys.extended_properties ON

  ( sys.extended_properties.minor_id = syscolumns.colid

    AND sys.extended_properties.major_id = syscolumns.id)

  LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id

  WHERE syscolumns.id IN

    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')

    ORDER BY syscolumns.colid

GO

--2.SQL查询表的所有字段的备注说明

SELECT

(case when a.colorder=1 then d.name else '' end) N'表名',

a.colorder N'字段序号',

a.name N'字段名',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''

end) N'标识',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

           (SELECT name

          FROM sysindexes

          WHERE (id = a.id) AND (indid in

                    (SELECT indid

                   FROM sysindexkeys

                   WHERE (id = a.id) AND (colid in

                             (SELECT colid

                            FROM syscolumns

                            WHERE (id = a.id) AND (name = a.name))))))) AND

        (xtype = 'PK'))>0 then '√' else '' end) N'主键',

b.name N'类型',

a.length N'占用字节数',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',

(case when a.isnullable=1 then '√'else '' end) N'允许空',

isnull(e.text,'') N'默认值',

isnull(g.[value],'') AS N'字段说明'

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' and d.name<>'dtproperties'

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

order by object_name(a.id),a.colorder

GO

--视图

SELECT

(case when a.colorder=1 then d.name else '' end) N'表名',

a.colorder N'字段序号',

a.name N'字段名',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''

end) N'标识',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

           (SELECT name

          FROM sysindexes

          WHERE (id = a.id) AND (indid in

                    (SELECT indid

                   FROM sysindexkeys

                   WHERE (id = a.id) AND (colid in

                             (SELECT colid

                            FROM syscolumns

                            WHERE (id = a.id) AND (name = a.name))))))) AND

        (xtype = 'PK'))>0 then '√' else '' end) N'主键',

b.name N'类型',

a.length N'占用字节数',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',

(case when a.isnullable=1 then '√'else '' end) N'允许空',

isnull(e.text,'') N'默认值',

isnull(g.[value],'') AS N'字段说明'

FROM syscolumns a

left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='V' and d.name<>'dtproperties'  --

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

order by object_name(a.id),a.colorder

GO

---函數（表函数呢）

SELECT

(case when a.colorder=1 then d.name else '' end) N'函数名',

a.colorder N'参数序号',

a.name N'参数名',

isoutparam N'输入输出',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''

end) N'标识',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

           (SELECT name

          FROM sysindexes

          WHERE (id = a.id) AND (indid in

                    (SELECT indid

                   FROM sysindexkeys

                   WHERE (id = a.id) AND (colid in

                             (SELECT colid

                            FROM syscolumns

                            WHERE (id = a.id) AND (name = a.name))))))) AND

        (xtype = 'PK'))>0 then '√' else '' end) N'主键',

b.name N'类型',

a.length N'占用字节数',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',

(case when a.isnullable=1 then '√'else '' end) N'允许空',

isnull(e.text,'') N'默认值',

isnull(g.[value],'') AS N'参数说明'

FROM syscolumns a

left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='FN' and d.name<>'dtproperties'

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

order by object_name(a.id),a.colorder

GO

--存储过程

SELECT

(case when a.colorder=1 then d.name else '' end) N'函数名',

a.colorder N'参数序号',

a.name N'参数名',

isoutparam N'输入输出',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''

end) N'标识',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

           (SELECT name

          FROM sysindexes

          WHERE (id = a.id) AND (indid in

                    (SELECT indid

                   FROM sysindexkeys

                   WHERE (id = a.id) AND (colid in

                             (SELECT colid

                            FROM syscolumns

                            WHERE (id = a.id) AND (name = a.name))))))) AND

        (xtype = 'PK'))>0 then '√' else '' end) N'主键',

b.name N'类型',

a.length N'占用字节数',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',

(case when a.isnullable=1 then '√'else '' end) N'允许空',

isnull(e.text,'') N'默认值',

isnull(g.[value],'') AS N'参数说明'

FROM syscolumns a

left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='P' and d.name<>'dtproperties'

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

order by object_name(a.id),a.colorder

GO

---

SELECT id,xtype,name FROM sysobjects 

--http://www.sqlservercentral.com/articles/Metadata/72607/

--https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

--3. SQL 2005查询表的所有字段的备注说明（要考虑存储过，函数，视图，索引，事务等）

SELECT

TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,

TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),

Column_id=C.column_id,

ColumnName=C.name,

PrimaryKey=ISNULL(IDX.PrimaryKey,N''),

[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,

Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,

Type=T.name,

Length=C.max_length,

Precision=C.precision,

Scale=C.scale,

NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,

[Default]=ISNULL(D.definition,N''),

ColumnDesc=ISNULL(PFD.[value],N''),

IndexName=ISNULL(IDX.IndexName,N''),

IndexSort=ISNULL(IDX.Sort,N''),

Create_Date=O.Create_Date,

Modify_Date=O.Modify_date

FROM sys.columns C

INNER JOIN sys.objects O

ON C.[object_id]=O.[object_id]

AND O.type='U' --表

AND O.is_ms_shipped=0

INNER JOIN sys.types T

ON C.user_type_id=T.user_type_id

LEFT JOIN sys.default_constraints D

ON C.[object_id]=D.parent_object_id

AND C.column_id=D.parent_column_id

AND C.default_object_id=D.[object_id]

LEFT JOIN sys.extended_properties PFD

ON PFD.class=1

AND C.[object_id]=PFD.major_id

AND C.column_id=PFD.minor_id

-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)

LEFT JOIN sys.extended_properties PTB

ON PTB.class=1

AND PTB.minor_id=0

AND C.[object_id]=PTB.major_id

-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)

LEFT JOIN -- 索引及主键信息

(

SELECT

IDXC.[object_id],

IDXC.column_id,

Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')

WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,

PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,

IndexName=IDX.Name

FROM sys.indexes IDX

INNER JOIN sys.index_columns IDXC

ON IDX.[object_id]=IDXC.[object_id]

AND IDX.index_id=IDXC.index_id

LEFT JOIN sys.key_constraints KC

ON IDX.[object_id]=KC.[parent_object_id]

AND IDX.index_id=KC.unique_index_id

INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息

(

SELECT [object_id], Column_id, index_id=MIN(index_id)

FROM sys.index_columns

GROUP BY [object_id], Column_id

) IDXCUQ

ON IDXC.[object_id]=IDXCUQ.[object_id]

AND IDXC.Column_id=IDXCUQ.Column_id

AND IDXC.index_id=IDXCUQ.index_id

) IDX

ON C.[object_id]=IDX.[object_id]

AND C.column_id=IDX.column_id

 --WHERE O.name=N'geovindu' -- 如果只查询指定表,加上此条件

ORDER BY O.name,C.column_id

GO

--4.--sql server 2005,2008,2012

SELECT

  表名=case   when   a.colorder=1   then   d.name   else   ''   end,

  表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,

  字段序号=a.colorder,

  字段名=a.name,

  标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end,

  主键=case   when   exists(SELECT   1   FROM   sys.sysobjects   where   xtype='PK'   and   name   in   (

  SELECT   name   FROM   sys.sysindexes   WHERE   indid   in(

  SELECT   indid   FROM   sys.sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid

  )))   then   '√'   else   ''   end,

  类型=b.name,

  占用字节数=a.length,

  长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),

  小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

  允许空=case   when   a.isnullable=1   then   '√'else   ''   end,

  默认值=isnull(e.text,''),

  字段说明=isnull(g.[value],'')

  FROM   sys.syscolumns   a

  left   join   sys.systypes   b   on   a.xusertype=b.xusertype

  inner   join   sys.sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'

  left   join   sys.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   --major_id

  left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0

  --where   d.name='要查询的表'         --如果只查询指定表,加上此条件

  order   by   a.id,a.colorder

GO

--5.--sql server 2000

SELECT

  表名=case   when   a.colorder=1   then   d.name   else   ''   end,

  表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,

  字段序号=a.colorder,

  字段名=a.name,

  标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end,

  主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (

  SELECT   name   FROM   sysindexes   WHERE   indid   in(

  SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid

  )))   then   '√'   else   ''   end,

  类型=b.name,

  占用字节数=a.length,

  长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),

  小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

  允许空=case   when   a.isnullable=1   then   '√'else   ''   end,

  默认值=isnull(e.text,''),

  字段说明=isnull(g.[value],'')

  FROM   syscolumns   a

  left   join   systypes   b   on   a.xusertype=b.xusertype

  inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'

  left   join   syscomments   e   on   a.cdefault=e.id

  left   join   sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid

  left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0  

  --where   d.name='要查询的表'         --如果只查询指定表,加上此条件

  order   by   a.id,a.colorder

GO

---20170329 edit: Geovin Du  涂聚文

--查询注释

select * from sys.extended_properties

go

--查询注释

SELECT      u.name + '.' + t.name AS [table],

            td.value AS [table_desc],

            c.name AS [column],

            cd.value AS [column_desc]

FROM        sysobjects t

INNER JOIN  sysusers u

    ON      u.uid = t.uid

LEFT OUTER JOIN sys.extended_properties td

    ON      td.major_id = t.id

    AND     td.minor_id = 0

    AND     td.name = 'MS_Description'

INNER JOIN  syscolumns c

    ON      c.id = t.id

LEFT OUTER JOIN sys.extended_properties cd

    ON      cd.major_id = c.id

    AND     cd.minor_id = c.colid

    AND     cd.name = 'MS_Description'

WHERE t.type = 'u'

ORDER BY    t.name, c.colorder

go

--查询注释

select

    TableName = tbl.table_schema + '.' + tbl.table_name,

    TableDescription = prop.value,

    ColumnName = col.column_name,

    ColumnDataType = col.data_type

FROM information_schema.tables tbl

INNER JOIN information_schema.columns col

    ON col.table_name = tbl.table_name

    AND col.table_schema = tbl.table_schema

LEFT JOIN sys.extended_properties prop

    ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name)

    AND prop.minor_id = 0

    AND prop.name = 'MS_Description'

WHERE tbl.table_type = 'base table'

go

--

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE],T.TYPE_DESC

FROM SYS.OBJECTS AS T

JOIN SYS.COLUMNS AS C

ON T.OBJECT_ID=C.OBJECT_ID

JOIN SYS.TYPES AS P

ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID

WHERE T.TYPE_DESC='USER_TABLE'

GO

--

SELECT

    TableName = tbl.table_schema + '.' + tbl.table_name,

    TableDescription = tableProp.value,

    ColumnName = col.column_name,

    ColumnDataType = col.data_type,

    ColumnDescription = colDesc.ColumnDescription

FROM information_schema.tables tbl

INNER JOIN information_schema.columns col

    ON col.table_name = tbl.table_name

LEFT JOIN sys.extended_properties tableProp

    ON tableProp.major_id = object_id(tbl.table_schema + '.' + tbl.table_name)

        AND tableProp.minor_id = 0

        AND tableProp.name = 'MS_Description'

LEFT JOIN (

    SELECT sc.object_id, sc.column_id, sc.name, colProp.[value] AS ColumnDescription

    FROM sys.columns sc

    INNER JOIN sys.extended_properties colProp

        ON colProp.major_id = sc.object_id

            AND colProp.minor_id = sc.column_id

            AND colProp.name = 'MS_Description'

) colDesc

    ON colDesc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)

        AND colDesc.name = col.COLUMN_NAME

WHERE tbl.table_type = 'base table'

--AND tableProp.[value] IS NOT NULL OR colDesc.ColumnDescription IS NOT null

GO

--

SELECT

COL.COLUMN_NAME,

ORDINAL_POSITION,

DATA_TYPE,

CHARACTER_MAXIMUM_LENGTH,

NUMERIC_PRECISION,

NUMERIC_PRECISION_RADIX,

NUMERIC_SCALE,

DATETIME_PRECISION,

IS_NULLABLE,

CONSTRAINT_TYPE,

COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsIdentity') IS_IDENTITY,

COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsComputed') IS_COMPUTED

FROM INFORMATION_SCHEMA.COLUMNS COL

LEFT OUTER JOIN

(

    SELECT COLUMN_NAME, CONSTRAINT_TYPE

    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B

    ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

    WHERE A.TABLE_NAME = 'DuUserPermission'

) CONS

ON COL.COLUMN_NAME = CONS.COLUMN_NAME

WHERE COL.TABLE_NAME = 'DuUserPermission'

go

--https://github.com/TheDev6/SqlMeta

--http://stackoverflow.com/questions/887370/sql-server-extract-table-meta-data-description-fields-and-their-data-types

---存储过程或自定义函数

declare @ProcName nvarchar(100),@Schema nvarchar(100)

set @Schema='dbo'

set @ProcName='f_GetCardRecordIs' --proc_Insert_DuConfigOutput

SELECT

                    SCHEMA_NAME(schema_id) AS [Schema]

                ,   P.Name AS Name

                ,   @ProcName AS ProcedureName

                ,   TYPE_NAME(P.user_type_id) AS [ParameterDataType]

                ,   P.max_length AS [MaxLength]

                ,   P.Precision AS [Precision]

                ,   P.Scale AS Scale

                ,   P.has_default_value AS HasDefaultValue

                ,   P.default_value AS DefaultValue

                ,   P.object_id AS ObjectId

                ,   P.parameter_id AS ParameterId

                ,   P.system_type_id AS SystemTypeId

                ,   P.user_type_id AS UserTypeId

                ,   P.is_output AS IsOutput

                ,   P.is_cursor_ref AS IsCursor

                ,   P.is_xml_document AS IsXmlDocument

                ,   P.xml_collection_id AS XmlCollectionId

                ,   P.is_readonly AS IsReadOnly

                FROM sys.objects AS SO

                INNER JOIN sys.parameters AS P ON SO.object_id = P.object_id

                WHERE SO.object_id IN (SELECT

                            object_id

                        FROM sys.objects

                        WHERE type IN ('P', 'FN'))

                    AND (SO.Name = @ProcName

                        OR @ProcName IS NULL)

                    AND (SCHEMA_NAME(schema_id) = @Schema

                        OR @Schema IS NULL)

                ORDER BY P.parameter_id ASC

GO

SELECT name

                    FROM dbo.sysobjects

                    WHERE xtype = 'U'

                    AND name <> 'sysdiagrams'

                    order by name asc

GO

declare @TableName nvarchar(100),@Schema nvarchar(100)

set @Schema='dbo'

set @TableName='DuDeptPermissionType'

SELECT

                    c.TABLE_CATALOG AS [TableCatalog]

                ,   c.TABLE_SCHEMA AS [Schema]

                ,   c.TABLE_NAME AS [TableName]

                ,   c.COLUMN_NAME AS [ColumnName]

                ,   c.ORDINAL_POSITION AS [OrdinalPosition]

                ,   c.COLUMN_DEFAULT AS [ColumnDefault]

                ,   c.IS_NULLABLE AS [Nullable]

                ,   c.DATA_TYPE AS [DataType]

                ,   c.CHARACTER_MAXIMUM_LENGTH AS [CharacterMaxLength]

                ,   c.CHARACTER_OCTET_LENGTH AS [CharacterOctetLenth]

                ,   c.NUMERIC_PRECISION AS [NumericPrecision]

                ,   c.NUMERIC_PRECISION_RADIX AS [NumericPrecisionRadix]

                ,   c.NUMERIC_SCALE AS [NumericScale]

                ,   c.DATETIME_PRECISION AS [DatTimePrecision]

                ,   c.CHARACTER_SET_CATALOG AS [CharacterSetCatalog]

                ,   c.CHARACTER_SET_SCHEMA AS [CharacterSetSchema]

                ,   c.CHARACTER_SET_NAME AS [CharacterSetName]

                ,   c.COLLATION_CATALOG AS [CollationCatalog]

                ,   c.COLLATION_SCHEMA AS [CollationSchema]

                ,   c.COLLATION_NAME AS [CollationName]

                ,   c.DOMAIN_CATALOG AS [DomainCatalog]

                ,   c.DOMAIN_SCHEMA AS [DomainSchema]

                ,   c.DOMAIN_NAME AS [DomainName]

                ,   IsPrimaryKey = CONVERT(BIT, (SELECT

                            COUNT(*)

                        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

                            ,   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu

                        WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

                        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME

                        AND tc.TABLE_NAME = c.TABLE_NAME

                        AND cu.TABLE_SCHEMA = c.TABLE_SCHEMA

                        AND cu.COLUMN_NAME = c.COLUMN_NAME)

                    )

                ,   IsIdentity = CONVERT(BIT, (SELECT

                            COUNT(*)

                        FROM sys.objects obj

                        INNER JOIN sys.COLUMNS col

                            ON obj.object_id = col.object_id

                        WHERE obj.type = 'U'

                        AND obj.Name = c.TABLE_NAME

                        AND col.Name = c.COLUMN_NAME

                        AND col.is_identity = 1)

                    )

                FROM INFORMATION_SCHEMA.COLUMNS c

                WHERE (@Schema IS NULL

                        OR c.TABLE_SCHEMA = @Schema)

                    AND (@TableName IS NULL

                        OR c.TABLE_NAME = @TableName)

 GO

declare @Table nvarchar(100),@Schema nvarchar(100)

set @Schema='dbo'

set @Table='DuDeptPermissionType'

 SELECT

                        KCU1.CONSTRAINT_NAME AS [ConstraintName]

                    ,   KCU1.TABLE_NAME AS [TableName]

                    ,   KCU1.COLUMN_NAME AS [ColumnName]

                    ,   KCU2.CONSTRAINT_NAME AS [UniqueConstraintName]

                    ,   KCU2.TABLE_NAME AS [UniqueTableName]

                    ,   KCU2.COLUMN_NAME AS [UniqueColumnName]

                    ,   RC.MATCH_OPTION AS [MatchOption]

                    ,   RC.UPDATE_RULE AS [UpdateRule]

                    ,   RC.DELETE_RULE AS [DeleteRule]

                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

                    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG

                        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

                        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

                    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG

                        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA

                        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

                    WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION

                            AND (@Table IS NULL

                                OR KCU1.TABLE_NAME = @Table

                                OR KCU2.TABLE_NAME = @Table)

                            AND (@Schema IS NULL

                                OR KCU1.TABLE_SCHEMA = @Schema

                                OR KCU2.TABLE_SCHEMA = @Schema)

GO

declare @TableName nvarchar(100),@Schema nvarchar(100)

set @Schema='dbo'

set @TableName='DuDeptPermissionType'

SELECT

                    B.COLUMN_NAME

                FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A

                    ,   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B

                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

                    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

                    AND A.TABLE_NAME = @TableName

                    AND A.TABLE_SCHEMA = @Schema

GO

巴特西

sql: table,view,function, procedure created MS_Description in sql server

最新文章

热门文章