不同类型数据库检查列属性语句1.txt-CSDN博客

今天给同事以前写的检查语句给记录下来

[@more@]

-- oracle 所有版本都适用
Select a.Table_Name 表名,e.comments 表注释, a.Column_Name 字段代码,
a.Data_Type || '(' || Data_Length || ')' 字段类型,
Decode(Constraint_Type, 'P', 'Y', Constraint_Type) 是否主键,
Nullable 是否非空,
d.Comments 字段说明
From User_Tab_Columns a,
(Select c.table_name,c.column_name,constraint_type From User_Constraints b,
User_Cons_Columns c Where b.Constraint_Name = c.Constraint_Name And constraint_type = 'P') c,
User_Col_Comments d,user_tab_comments e
Where a.Table_Name = c.Table_Name(+)
And a.Column_Name = c.Column_Name(+)
And a.Table_Name = d.Table_Name
And a.Column_Name = d.Column_Name
And a.table_name = e.table_name
Order By 1,a.column_id

--sql server 2000
SELECT 表名=case when a.colorder=1 then d.name else '' end,
表说明= case when a.colorder = 1 and h.smallid = 0 then isnull(h.[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.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 outer join sysproperties g on a.id=g.id
and a.colid=g.smallid left join (select * from sysproperties where smallid = 0) h on a.id=h.id
order by a.id,a.colorder

--Sql Server 2005 版本：
SELECT 表名=case when a.colorder=1 then d.name else '' end,
表说明= case when a.colorder = 1 and h.minor_id = 0 then isnull(h.[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.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 left join (select * from sys.extended_properties where minor_id = 0) h on a.id=h.major_id
order by a.id,a.colorder

# mysql 版本
SELECT a.Table_Name 表名,a.table_comment 表注释,b.ordinal_position 列序号,b.column_name 列名,
b.column_type 列类型, b.column_key 是否主键, b.is_nullable 是否非空,b.column_default 缺省值,b.column_comment 列注释
FROM TABLES a,COLUMNS b
WHERE a.Table_Name = b.Table_Name

--oracle数据库对象过程、函数、触发器、包体、类型体中注释要求，1、总体注释放在参数定义后面变量定义前面，如下所示；2、变更部分需要在结项以后如有变更再填写
create or replace ...(
)
/**************************************
'功能名称：描述该对象总体实现的功能
'作者:***
'创建日期:***
'变更人:*** 变更日期:yyyy-mm-dd 变更内容：(1.1)***;(1.2)$$$
'变更人:*** 变更日期:yyyy-mm-dd 变更内容：(2.1)***;(2.2)$$$
'**************************************/
declare/as/is
***
begin
...
(1.1)
...
(1.2)
...
(2.1)
...
(2.2)
...
end;