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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 | IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'enterFunctionNameHere' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'FUNCTION' ) DROP FUNCTION [dbo].[enterFunctionNameHere] GO |
3 Responses to “SQL: if exists”
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?