Feeds:
Posts
Comments

Posts Tagged ‘system’

--Check if a table exists (and drop it if does) before creating it again
IF EXISTS (
  SELECT NAME
  FROM anydbnameOptional.dbo.sysobjects -- use TYPE to specify tables, Sps etc
  WHERE type = 'u' -- <a href="http://msdn.microsoft.com/en-us/library/ms177596.aspx">http://msdn.microsoft.com/en-us/library/ms177596.aspx</a>
   AND NAME = 'whatever'
  )
 DROP TABLE whatever

--Check if a database exists
IF EXISTS (
  SELECT NAME
  FROM master..sysdatabases
  WHERE NAME = 'DatabaseName'
  )
 SELECT 'ok'

DECLARE @anotherWay VARCHAR(100)

-- if select is used then the outer bracket must be used as well
--set @anotherWay = (select DB_NAME())
SET @anotherWay = DB_NAME()

PRINT @anotherWay

--=====================
-- To check and delete an object
--if object_id ('dbo.udf_CapitaAnsweredCallsKPI8') is not null
--    drop function dbo.udf_CapitaAnsweredCallsKPI8
--go
----=========================
-------- =========== Check if a column exists  ================================ --------------------------
IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'myTableName'
   AND COLUMN_NAME = 'myColumnName'
  )
 ---- The below way (sys.columns) is depricated
 IF EXISTS (
   SELECT *
   FROM sys.columns
   WHERE NAME = N'columnName'
    AND Object_ID = Object_ID(N'tableName')
   )
 BEGIN
  -- Column Exists
 END
   ----------------------====================================================----------------------------------

Advertisements

Read Full Post »