Check if the SQL object exists

Source: Internet
Author: User

SQL Server determines whether an object exists
1 Determine if the database has SQL code
if exists (SELECT * from sys.databases WHERE name = ' database name ') drop database [DB name]
2 Determine if the table has SQL code
if exists (select * from sysobjects where id = object_id (' table name ') and OBJECTPROPERTY (ID, ' isusertable ') = 1) drop table [table name]
if exists (select * from sysobjects WHERE name = ' table name ' and xtype = ' U ') drop table [table name]
3 determine if the stored procedure has SQL code
if exists (select * from sysobjects where id = object_id (' [Stored procedure name] ') and OBJECTPROPERTY (ID, ' isprocedure ') = 1) Drop proced ure [Stored Procedure name]
if exists (select 1 from sysobjects where name = ' Stored procedure name ' and xtype = ' P ') drop procedure [Stored procedure name]
4 to determine if the temporary table has SQL code
If object_id (' tempdb.. #临时表名 ') is not a null drop table #临时表名
5 Judging whether the view has SQL code
--sql Server 2000
IF EXISTS (SELECT * from sysviews WHERE object_id = ' [dbo].[ View name] ')
--sql Server 2005
IF EXISTS (SELECT * from sys.views WHERE object_id = ' [dbo].[ View name] ')
6 determining if a function has SQL code
--Determine if the name of the function to be created exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (' [dbo].[ Function name] ') and xtype in (' FN ', ' IF ', ' TF ')


Wk_ad_begin ({pid:21}); Wk_ad_after (, function () {$ ('. Ad-hidden '). Hide ();}, function () {$ ('. Ad-hidden '). Show ();});


Drop function [dbo]. [function name]
if exists (select 1 from sysobjects where name = ' function name ' and xtype in (' FN ', ' IF ', ' TF ')) drop function [function name]
7 getting user-created object information SQL code
SELECT 1 from sysobjects where name = [object name] and xtype= '/*
The xtype represents the parameter types, usually including the following C = CHECK constraints
D = defaults or default constraint F = FOREIGN KEY constraint L = log
FN = Scalar function IF = inline table function P = stored procedure
PK = PRIMARY KEY constraint (type is K) RF = copy Filter stored procedure S = system table TF = table function TR = trigger U = User table
UQ = UNIQUE constraint (type is K) V = view
X = Extended Stored procedure */
8 Determine if the column has SQL code
if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ') ALTER TABLE table name drop column name
9 Determining if a column is self-adding SQL code
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1 print ' self-increment ' else
print ' is not self-adding column '
SELECT * from syscolumns WHERE object_id=object_id (' table name ') and Is_identity=1



10 determine if the index SQL code exists in the table
if exists (SELECT * from sysindexes where id=object_id (' table name ') and name= ' index name ') print ' exists ' else
print ' does not exist '
11 Viewing object SQL code in the database
SELECT * from sys.sysobjects WHERE name= ' object name '
12 See if the primary key exists in the table and, if present, drop it declare @pk varchar (100)
Select @pk =name from sysobjects where parent_obj=object_id (' table name ') and xtype= ' PK ' if @pk is not null
EXEC (' ALTER TABLE table name drop ' + @pk)

Check if the SQL object exists

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.