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:
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:
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:
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:
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?
Great job once again!! Thank you.
can u plz explain the function of if exists.
Why we use if exists.
And also explain the query if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Addamenities]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[Addamenities]
GO