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

Source: Internet
Author: User
Tags scalar

SQL determines if stored procedure exists SQL code for database tutorialif exists(Select *  fromsys.databaseswhereName=' database name ')Drop Database [Database name]  if exists(Select *  fromsys.databaseswhereName=' database name ')Drop Database [Database name]Determine if the table has SQL codeif exists(Select *  fromsysobjectswhereId= object_idN[Table name]’) and ObjectProperty(ID, N ' isusertable ')= 1)    Drop Table [Table name]  if exists(Select *  fromsysobjectswhereId= object_idN[Table name]’) and ObjectProperty(ID, N ' isusertable ')= 1)  Drop Table [Table name]determine if the stored procedure has SQL codeif exists(Select *  fromsysobjectswhereId= object_idN[Stored Procedure name]’) and ObjectProperty(ID, N ' isprocedure ')= 1)    Drop procedure [Stored Procedure name]  if exists(Select *  fromsysobjectswhereId= object_idN[Stored Procedure name]’) and ObjectProperty(ID, N ' isprocedure ')= 1)  Drop procedure [Stored Procedure name]determine if the temporary table has SQL codeif object_id(' tempdb: #临时表名 ') is  not NULL      Drop Table#临时表名if object_id(' tempdb: #临时表名 ') is  not NULL    Drop Table#临时表名判断视图是否存在Sql代码--SQL ServerIF EXISTS(SELECT *  fromSysviewsWHERE object_id =’[dbo].[View name]'--SQL Server 2005IF EXISTS(SELECT *  fromSys.viewsWHERE object_id =’[dbo].[View name]’--SQL ServerIF EXISTS(SELECT *  fromSysviewsWHERE object_id =’[dbo].[View name]'--SQL Server 2005IF EXISTS(SELECT *  fromSys.viewsWHERE object_id =’[dbo].[View name]' Determine if the function has SQL code--determine if the name of the function to be created exists  if exists(Select *  fromDbo.sysobjectswhereId= object_idN[dbo].[Name of function]’) andXtypeinch(n ' FN ', n 'IF', N ' TF ')) Drop function [dbo].[Name of function]    --determine if the name of the function to be created exists  if exists(Select *  fromDbo.sysobjectswhereId= object_idN[dbo].[Name of function]’) andXtypeinch(n ' FN ', n 'IF', N ' TF ')) Drop function [dbo].[Name of function]get user-created object information SQL codeSELECT [name],[ID], Crdate fromsysobjectswhereXtype=' U '/*the xtype represents the parameter type, usually including the following C = CHECK constraint D = default value or defaults constraint F = FOREIGN KEY constraint L = log FN = scalar function IF = inline table function P = Store Process 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 k) V = view X = Extended Stored Procedures*/  SELECT [name],[ID], Crdate fromsysobjectswhereXtype=' U '/*the xtype represents the parameter type, usually including the following c = CHECK constraint D = default value or defaults constraint f = FOREIGN KEY constraint l = log fn = scalar function if = inline table function p = Stored procedure pk = PRIMARY K EY constraint (type is k) RF = Copy Filter stored procedure s = system table TF = Table function TR = trigger u = User table UQ = UNIQUE constraint (type k) V = view x = Extended stored Procedure*/determine if the column has SQL codeif exists(Select *  fromsyscolumnswhereId=object_id(' Table name ') andName=' column name ')Alter TableTable nameDrop columnColumn Nameif exists(Select *  fromsyscolumnswhereId=object_id(' Table name ') andName=' column name ')Alter TableTable nameDrop columncolumn name Determines whether the column is self-increment SQL codeif ColumnProperty(object_id(’Table'), ' col ', ' isidentity ')=1    Print' self-increment column 'Else    Print' not self-increment 'SELECT *  fromSys.columnsWHERE object_id=object_id(' table name ') andIs_identity=1  if ColumnProperty(object_id(’Table'), ' col ', ' isidentity ')=1  Print' self-increment column 'Else  Print' not self-increment 'SELECT *  fromSys.columnsWHERE object_id=object_id(' table name ') andIs_identity=1determine if the index SQL code exists in the tableif exists(Select *  fromsysindexeswhereId=object_id(' Table name ') andName=' index name ')Print' existence 'Else      Print' does not existif exists(Select *  fromsysindexeswhereId=object_id(' Table name ') andName=' index name ')Print' existence 'Else    Print' does not exist view object SQL code in databaseSELECT *  fromSys.sysobjectsWHEREName=' Object name 'SELECT *  fromSys.sysobjectsWHEREName=' Object name '

Excerpt from: http://www.111cn.net/database/mssqlserver/39107.htm

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

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.