数据库判断表、视图、存储过程是否存在语句

1.判断某个存储过程是否存在

if exists(select 1 from sysobjects where name = 'XXXXXX' and xtype = 'P')
begin
drop proc XXXXXX
end
go

 

2.判断某张表是否存在

if not Exists(select 1 from sysObjects where Id=OBJECT_ID(N'XXXXXX') and xtype='U')

 

3.判断某个视图是否存在

if exists(select 1 from sysobjects where name = 'XXXXXX' and xtype = 'V')
BEGIN
drop view XXXXXX
END
GO

 

4.判断表中某一列是否存在，若不存在则添加

IF NOT EXISTS(SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('XXXXXX') AND NAME='XXXXXX')
ALTER TABLE XXXXXX ADD XXXXXX varchar(100)
GO

 

5.给表添加注释

EXEC sys.sp_addextendedproperty
@name=N'MS_Description', @value=N'表格注释内容' ,
@level0type=N'SCHEMA',@level0name=N'选择dbo或者guest',
@level1type=N'TABLE',@level1name=N'表格名称';

 

6.查询表说明

SELECT t.name AS 表名,c.value AS 说明
FROM sys.tables t
INNER JOIN sys.extended_properties c ON t.object_id = c.major_id AND c.minor_id = 0;

7.查询表字段说明

 SELECT t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value]
  as varchar(100)) AS [字段说明]
  FROM sys.tables AS t
  INNER JOIN sys.columns
  AS c ON t.object_id = c.object_id
  LEFT JOIN sys.extended_properties AS ep
  ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1
 AND t.name='xxxx'--------表名