SQL Server Table Property query

Get table information:

SelectSyscolumns.name asfield, Syscolumns.isnullable asNullis, Systypes.name asSqlType, syscolumns.[length]  asLenth,ISNULL(Sys.identity_columns.is_identity,0) asIdenti,ISNULL(Sys.extended_properties.value,"') asSummary fromsysobjectsJoinsyscolumns onSysobjects.id=syscolumns.idJoinSystypes onSyscolumns.xusertype=Systypes.xusertype Left JoinSys.identity_columns onSys.identity_columns.object_id =Syscolumns.id andsys.identity_columns.column_id=Syscolumns.colid Left JoinSys.extended_properties onsys.extended_properties.major_id=syscolumns.id andsys.extended_properties.minor_id=Syscolumns.colidwhereSysobjects.name= 'TableName'

To determine whether a table exists:

if exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [TableName]') and ObjectProperty(ID, N'isusertable')= 1)      Select 'true'Else    Select 'false'  

<p><strong> get the primary key table name based on the table foreign key name </strong></p>

SELECTforeign key table ID=B.fkeyid, foreign key table name= object_name(B.fkeyid), foreign key column ID=B.fkey, foreign key column name=(SELECTName fromsyscolumnsWHEREColid=B.fkey andId=B.fkeyid), primary key table ID=B.rkeyid, primary key table name= object_name(B.rkeyid), primary key column ID=B.rkey, primary key column name=(SELECTName fromsyscolumnsWHEREColid=B.rkey andId=B.rkeyid), cascade update= ObjectProperty(a.ID,'Cnstisupdatecascade'), cascade delete= ObjectProperty(a.ID,'Cnstisdeletecascade' )  fromsysobjects aJoinSysforeignkeys b ona.ID=B.constidJoinsysobjects C onA.parent_obj=c.idwhereA.xtype= 'F'  andC.xtype= 'U'  andA.name= 'News' Select object_name(B.fkeyid), foreign key column name=(SELECTName fromsyscolumnsWHEREColid=B.fkey andId=B.fkeyid) fromsysobjects asAJoinSysforeignkeys asB ona.ID=B.constidwhereA.xtype='F'

Determine if the field already exists in the table

if exists(Select *  fromsyscolumnswhereId=object_id('Table') andName='Cloumn')Select 'true' Else Select 'false'

Determine if the field already exists in the table _oracle

Select 1  from where = Upper ('studentinfo'and=Upper('class ');

Delete all indexes in the specified table

/*deletes all indexes for the specified table, including primary key indexes, unique indexes, and normal index calls: Declare @tbName varchar set @tbName = ' Cp_pathinfo ' exec sp_dropind Ex @tbName vivianfdlpw 2005.9 quote Keep this information*/if exists(Select 1  fromsysobjectswhereId=object_id('Sp_dropindex') andXtype='P')Drop procedureSp_dropindexGoCreate procedureSp_dropindex@tbName varchar( -)=NULL   --Index name asif @tbName  is NULLbegin     RAISERROR('The @tbname parameter must be supplied', A,1)     returnEndCreate Table# (IDint Identity, Index_namevarchar( -), Index_descriptionvarchar( +), Index_keysvarchar( -))Insert# (Index_name,index_description,index_keys)execSp_helpindex@tbNameDeclare @i int,@sql varchar( -)Set @i=1 while @i<=(Select Max(ID) from #)begin      if exists(Select 1                  fromsysobjects AJoin# B onA.name=B.index_namewhereb.ID=@i  andA.xtypeinch('PK','UQ'))      begin           Select @sql='ALTER TABLE'+@tbName+'drop Constraint'                       +(SelectIndex_name from#whereId=@i)           exec(@sql)      End      Else      begin           Select @sql='DROP Index'+@tbName+'.'                       +(SelectIndex_name from#whereId=@i)           exec(@sql)      End       Set @i=@i+1EndDrop Table #GoCreate IndexIx_sacag oncp_pathinfo (remind_today)Declare @tbName varchar( -)    Set @tbName='Cp_pathinfo'    execSp_dropindex@tbName

SQL Server Table Property query