SQL Server determines if there is a database, table, column, view

Source: Internet
Author: User

Original: SQL Server determines if there is a database, table, column, view

1 determining whether a database exists
if exists (SELECT * from sys.databases WHERE name = ' database name ')
drop database [DB name]

2 Determining whether a table exists
if exists (select * from sysobjects where id = object_id (n ' [table name] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)
drop table [table name]

3 determining if a stored procedure exists
if exists (select * from sysobjects where id = object_id (n ' [Stored procedure name] ') and OBJECTPROPERTY (ID, n ' isprocedure ') = 1)
drop procedure [Stored procedure name]


4 determining whether a temporary table exists
If object_id (' tempdb.. #临时表名 ') is not null
drop table #临时表名

5 Judging whether the view exists

--To determine if there is ' MyView52 ' this attempt
IF EXISTS (SELECT table_name from INFORMATION_SCHEMA. Views WHERE table_name = N ' MyView52 ')
PRINT ' exists '
Else
PRINT ' does not exist '
6 determining whether a function exists
--Determine if the name of the function to be created exists
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Function name] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [function name]

7 getting user-created object information

SELECT [name],[id],crdate from sysobjects where xtype= ' U '


8 determining whether a column exists
if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')
ALTER TABLE table name drop column name

9 Judging whether a column is self-increment
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-increment column '
Else
print ' is not self-adding column '

SELECT * from Sys.columns WHERE object_id=object_id (' table name ') and Is_identity=1

10 determine if an index 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 '

Delete index drop the index table name. Indexed name

Or: Drop index index name on table name (seemingly 2000 not)

11 viewing objects in a database

SELECT * from sys.sysobjects where name= ' object name ' SELECT * from sys.sysobjects where name= ' object name '

Related Article

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.