sql 系统表应用

#region查询表字段类型  

select systypes.[name],systype.rn from systypes  --类型表  inner join  ( select a.xtype,a.val,row_number() over (partition by val order by a.val desc)rn from ( select xtype,1 val from syscolumns where id in (select id from sysobjects where [name] = ' tabName ')) a ) --得到表字段对应的类型  systype on systypes.xtype = systype.xtype order by rn;

#endregion    

#region查询表中的字段名称  

  select [name] from syscolumns where id in (select id from sysobjects where [name] = 'tabName')"; 

 #endregion

#region清除库的表

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[permission ].[ tabName ]') AND type in ('U')) DROP TABLE [permission].[tabName]    

#endregion  

#region 得到库中表名称  

select s.name,o.name tbname from sysobjects o,sysusers s where o.uid = s.uid and xtype = 'u'  #endreigon 

 #region 删除函数

 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[permission]. [funName]') AND type in ('FN', 'IF', 'TF', 'FS', 'FT')) DROP FUNCTION [permission].[funName]

#endregion  

#region 删除存储过程 

 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[permission].[procName]') AND type in ('P', 'PC')) DROP PROCEDURE [permission].[procName] #endregion   #region  将库中函数读取出来 select c.name,b.text,a.name from sysobjects a inner join syscomments b on a.id = b.id inner join sysusers c on a.uid = c.uid where a.xtype in ('FN', 'IF', 'TF', 'FS', 'FT') 

#endregion  

  #region   读取库中存储过程信息

select b.name permission,c.text,a.name  from sysobjects a inner join sysusers b  on b.uid = a.uid inner join syscomments c on a.id = c.id  where  a.xtype = 'p' 

 #endregion