SQL判断临时表及判断某列是否存在

--判断临时表是否存在
IF object_id(N'tempdb.dbo.#t')is not null
--IF object_id('tempdb.dbo.#t') is not null 
--IF Object_id('Tempdb..#t') IS NOT NULL
drop table #t

 SQL——Sql_Server中如何判断表中某列是否存在

--参考：http://blog.csdn.net/yenange/article/details/7276547
--比如说要判断表A中的字段C是否存在两个方法：  
--一，  
IF EXISTS (  
 SELECT 1 FROM SYSOBJECTS T1  
  INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID  
 WHERE T1.NAME='A' AND T2.NAME='C'  
 )  
 PRINT '存在'  
  ELSE  
 PRINT '不存在'  
   
--二， 短小精悍，可谓精典  
IF COL_LENGTH('A', 'C') IS NOT NULL  
    PRINT N'存在'  
ELSE  
    PRINT N'不存在'  
  
<p></p>方法一：   
select   *   from   syscolumns   where   id=object_id('表名')   and   name='列名'  
说明：存在则返回此列的一条说明记录，不存在返回空；  
  
方法二：  
select   count(*)   from   sysobjects   a,syscolumns   b  where a.id=b.id  and  b.name='flag1'  and a.type='u'   and   a.name='T_Pro_ProductClass'  
说明：存在返回1,不存在则返回0  

 

IF NOT EXISTS ( SELECT  NULL
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[TestTableName]')
                        AND OBJECTPROPERTY(id, 'IsTable') = 1 )
    BEGIN
        CREATE TABLE PreHeader
            (
              PrePackagedId [UNIQUEIDENTIFIER] NOT NULL ,
              WauseId [UNIQUEIDENTIFIER] NOT NULL ,
              PrePackagedCodeNumber [VARCHAR](50) NOT NULL ,
              PreCodeNumber [VARCHAR](50) NOT NULL ,         
              Disabled INT
                NOT NULL
                CONSTRAINT [DF_ PreHeader _Disabled] DEFAULT ( (0) ) ,
              CateTime DATETIME
                NULL
                CONSTRAINT [DF_ PreHeader _CateTime ]
                DEFAULT ( GETDATE() ) ,
              CreateBy [VARCHAR](50) NULL ,
              ModifyTime [DATETIME] NULL ,
              ModifyBy [VARCHAR](50) NULL ,
              CONSTRAINT [PK_ PreHeader] PRIMARY KEY CLUSTERED
                ( PrePackagedId ASC )
                WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                       ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
            )
        ON  [PRIMARY];       
        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'装名称', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PrePackage_Header',
            @level2type = N'COLUMN', @level2name = N'PackagName';
        EXEC sys.sp_addextendedproperty @name = N'MS_Description',
            @value = N'管理主表', @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'PreHeader';

    END;
GO

 

判断数据库、表、存储过程、视图、列 是否存在

http://www.cnblogs.com/shy1766IT/p/5274428.html