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

获得表信息：

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 summaryfrom 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" >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.constIDwhere 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_dropindexgocreate procedure sp_dropindex@tbname varchar(20)=null   --索引名asif @tbname is nullbegin     raiserror('必须提供@tbname参数',12,1)     returnendcreate 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 @tbnamedeclare @i int,@sql varchar(100)set @i=1while @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+1enddrop table #gocreate index IX_SACAG on CP_PATHINFO(	REMIND_TODAY)    declare @tbname varchar(20)    set @tbname='CP_PATHINFO'    exec sp_dropindex @tbname

总结

以上是内存溢出为你收集整理的SqlServer随笔全部内容，希望文章能够帮你解决SqlServer随笔所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。