--添加描述 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://*.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