oracle&&Sqlserver擷取表名列名主鍵及資料類型

標籤：io   資料   for   amp   sql   type   on   oracle   

SQlserver獲得列名，列類型，列類型長度，scale，prec等資料類型（syscolumns，systypes，sysobjects均為視圖）

select a.name as colname, b.name as typename,a.length as length,a.scale as scale,a.prec as prec from syscolumns a,systypes b ,sysobjects c where a.xusertype=b.xusertype and a.id=c.id and c.name =‘TableName‘ order by c.id

Oracle獲得列名，列類型，列類型長度，scale，prec等資料類型

select column_name,data_type ,data_length,data_precision,data_scale from user_tab_columns where   table_name=‘TableName‘

SQLserver獲得所有表名

Select Name FROM SysObjects Where XType=‘U‘

Oracle擷取所有表名

select tname  from tab where tabtype=‘TABLE‘

 

獲得表中主鍵的列，列類型，列類型長度

select a.name as colname, b.name as typename,a.length as length,a.scale as scale,a.prec as prec from syscolumns a,systypes b ,sysobjects c , INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d where a.xusertype=b.xusertype and a.id=c.id and c.name =‘TableName‘ and b.name<>‘DATE‘ and a.name =d.COLUMN_NAME and d.CONSTRAINT_NAME like ‘PK_%‘