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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

--添加描述 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

　　

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

--为函数添加描述信息 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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318

---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  涂聚文

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246

--查询注释 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