SQL: if exists
19Mar07
I collected these SQL scripts so that whenever I want to check the existency of a table, view, stored procedure, or function in a database, all I have to do is to copy and paste them to the Query Analyzer. FYI, These scripts are only used for SQL Server 
. Hope it's useful!
Check if the table exists in a database:
SQL:
-
IF EXISTS
-
(
-
SELECT * FROM dbo.sysobjects
-
WHERE id = object_id(N'[dbo].[enterTableNameHere]')
-
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
-
)
-
-
DROP TABLE [dbo].[enterTableNameHere]
-
-
GO
Check if the view exists in a database:
SQL:
-
IF EXISTS
-
(
-
SELECT *
-
FROM INFORMATION_SCHEMA.VIEWS
-
WHERE TABLE_NAME = 'enterViewNameHere'
-
)
-
-
DROP VIEW [dbo].[enterViewNameHere]
-
-
GO
Check if the stored procedure exists in a database:
SQL:
-
IF EXISTS
-
(
-
SELECT * FROM dbo.sysobjects
-
WHERE id = object_id(N'[dbo].[enterStoredProcedureNameHere]')
-
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
-
)
-
-
DROP PROCEDURE [dbo].[enterStoredProcedureNameHere]
-
-
GO
Check if the user-defined function exists in a database:
SQL:
-
IF EXISTS
-
(
-
SELECT *
-
FROM INFORMATION_SCHEMA.ROUTINES
-
WHERE ROUTINE_NAME = 'enterFunctionNameHere'
-
AND ROUTINE_SCHEMA = 'dbo'
-
AND ROUTINE_TYPE = 'FUNCTION'
-
)
-
-
DROP FUNCTION [dbo].[enterFunctionNameHere]
-
-
GO
cara menampilkan code kayak gitu gimana mel? di WP ada plugin nya kah?
ah, a fellow programmer… at last
is there a good way to do a
if exists ( selectStatementA )
begin
selectStatementA
end
?
It seems really clumsy to repeat the same select statement. or may be i’m not really supposed to use an “if exists” in this place o.o
can i haz help?