SQL Server determines whether there are databases, tables, columns, and views

test · Posted on 4/18/2015 9:45:43 AM

1 Determine whether the database exists
if exists (select * from sys.databases where name = 'database name')
  drop database [database name]
2 Judge whether the 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 Determine whether 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 Determine whether a temporary table exists
if object_id('tempdb.. #临时表名') is not null
  drop table #临时表名
5 Determine whether the view exists
- Determine if there is an attempt to 'MyView52'
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA. VIEWS WHERE TABLE_NAME = N'MyView52')
PRINT 'Exists'
else
PRINT 'does not exist'
6 Determine whether a function exists
-- Determine if the function name 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 Obtain the information of the object created by the user
SELECT [name],[id],crdate FROM sysobjects where xtype='U'

8 Determine 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 column name
9 Determine whether the column is self-incremented
if columnproperty(object_id('table'),'col','IsIdentity')=1
  print 'self-added'
else
  print 'not self-added'

SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('table name') AND is_identity=1
10 Determine whether 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'
11 View objects in the database
SELECT * FROM sys.sysobjects WHERE name='object name' SELECT * FROM sys.sysobjects WHERE name='object name'

yuyun100 · Posted on 4/24/2018 2:22:04 PM

Collection, very practical.

[Source] SQL Server determines whether there are databases, tables, columns, and views

Related Posts

Sections viewed