SqlServer随笔

获得表信息：

select 
	syscolumns.name as field,
	syscolumns.isnullable as nullis,
	systypes.name as sqltype,
	syscolumns.[length] as lenth,
	ISNULL(sys.identity_columns.is_identity,0) as identi,
	ISNULL(sys.extended_properties.value,'') as summary
from sysobjects 
	join syscolumns on sysobjects.id = syscolumns.id
	join systypes on syscolumns.xusertype = systypes.xusertype 
	left join sys.identity_columns 
	on sys.identity_columns.object_id = syscolumns.id and sys.identity_columns.column_id = syscolumns.colid 
	left join sys.extended_properties on sys.extended_properties.major_id = syscolumns.id
	and sys.extended_properties.minor_id = syscolumns.colid 
where sysobjects.name = 'TableName'

判断表是否存在：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  
	select 'true'
else
	select 'false'  

</pre><p><strong>根据表外键名称获得主键表名称</strong></p><p><pre name="code" class="sql">SELECT
外键表ID = b.fkeyid ,
外键表名称 = object_name (b.fkeyid) ,
外键列ID = b.fkey ,
外键列名 = ( SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,
主键表ID = b.rkeyid ,
主键表名= object_name (b.rkeyid) ,
主键列ID = b.rkey ,
主键列名 = ( SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) ,
级联更新 = ObjectProperty (a.id, ' CnstIsUpdateCascade ' ) ,
级联删除 = ObjectProperty (a.id, ' CnstIsDeleteCascade ' ) 
FROM sysobjects a 
 join sysforeignkeys b on a.id = b.constid 
 join sysobjects c on a.parent_obj = c.id 
where a.xtype = 'f' AND c.xtype = 'U' and a.name = 'News' 

select 
object_name (b.fkeyid),
外键列名 = ( SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid)
 from sysobjects as a join  sysforeignkeys as b on a.id=b.constid
where a.xtype='F'

判断字段是否在表中已存在

if exists(select * from syscolumns where id=object_id('table') and name='cloumn') select 'true' else select 'false'

判断字段是否在表中已存在_Oracle

select 1 from all_Tab_Columns where table_name = upper('studentinfo') and column_name = upper('class');

删除指定表中的所有索引

/*
    删除指定表的所有索引，包括主键索引，唯一索引和普通索引
 
    调用:
    declare @tbName varchar(20)
    set @tbName='CP_PATHINFO'
    exec sp_dropindex @tbName

    vivianfdlpw 2005.9 引用情保留此信息
*/
if exists(select 1 from sysobjects where id=object_id('sp_dropindex') and xtype='P')
drop procedure sp_dropindex
go
create procedure sp_dropindex
@tbName varchar(20)=null   --索引名
as

if @tbName is null
begin
     raiserror('必须提供@tbName参数',12,1)
     return
end

create table #
(
   id int identity,
   index_name varchar(50),
   index_description varchar(1000),
   index_keys varchar(100)
)
insert #(index_name,index_description,index_keys) 
exec sp_helpindex @tbName

declare @i int,@sql varchar(100)
set @i=1

while @i<=(select max(id) from #)
begin
      if exists(select 1 
                from sysobjects A 
                join # B on A.name=B.index_name
                where B.id=@i and A.xtype in ('PK','UQ'))
      begin
           select @sql='alter table '+@tbName+' drop constraint '
                       +(select index_name from # where id=@i)
           exec(@sql)
      end
      else
      begin
           select @sql='drop index '+@tbName+'.'
                       +(select index_name from # where id=@i)
           exec(@sql)
      end
 
      set @i=@i+1
end

drop table #

go

create index IX_SACAG on CP_PATHINFO
(
	REMIND_TODAY
)

    declare @tbName varchar(20)
    set @tbName='CP_PATHINFO'
    exec sp_dropindex @tbName