常用的创建表、视图、存储过程、索引、事务、作业可重复执行脚本

--创建表时判断表是否已存在，存在则删除
IF EXISTS(SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'UserInfo') AND TYPE in(N'U'))
    DROP TABLE UserInfo
GO
CREATE TABLE UserInfo
(
  Id INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(255) DEFAULT('') not null,
  Phone VARCHAR(255) DEFAULT('') not null
)
GO

--创建存储过程时，判断存储过程是否已存在，存在则删除
IF  EXISTS(SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[P_I_UserInfo]')AND TYPE in(N'P', N'PC'))
	DROP PROCEDURE [dbo].[P_I_UserInfo]
GO
CREATE PROCEDURE [dbo].[P_I_UserInfo]
(
  @Name NVARCHAR(255),
  @Phone VARCHAR(255)
)
AS
BEGIN
   INSERT INTO UserInfo(Name, Phone) VALUES (@Name,@Phone)
END
GO
--执行存储过程
EXECUTE P_I_UserInfo '李娜','13121824312'

--新增字段
IF NOT EXISTS(SELECT 1 FROM syscolumns WHERE id = OBJECT_ID('UserInfo') AND NAME = 'Address')
BEGIN
    ALTER TABLE UserInfo ADD Address NVARCHAR(255) NOT NULL DEFAULT('')
    EXEC sys.sp_addextendedproperty  @name=N'MS_Description', @value=N'家庭地址'
       , @level0type=N'SCHEMA', @level0name=N'dbo'
       , @level1type=N'TABLE', @level1name=N'UserInfo'
       , @level2type=N'COLUMN', @level2name=N'Address'
END
GO

--修改字段长度
IF EXISTS(SELECT 1 FROM syscolumns WHERE id = object_id('UserInfo') AND name = 'Address')
BEGIN
    ALTER TABLE UserInfo ALTER COLUMN Address NVARCHAR(800) NOT NULL 
END
GO

--删除字段默认约束&修改字段类型&长度&增加默认约束
DECLARE @tablename VARCHAR(100), @columnname VARCHAR(100), @tab VARCHAR(100)
SET @tablename = 'UserInfo'    --表名
SET @columnname= 'Name'    --字段名称
 
DECLARE @defname VARCHAR(100)    --约束名称
DECLARE @cmd VARCHAR(100)    --构造的SQL语句
SELECT @defname = NAME FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE OBJECT_NAME(so.parent_obj) = @tablename 
AND so.xtype = 'd' AND sc.colid =(SELECT colid FROM syscolumns WHERE id = OBJECT_ID(@tablename) and NAME = @columnname) 
SELECT @cmd= 'ALTER TABLE '+ @tablename+ ' DROP CONSTRAINT '+ @defname if @cmd is null print ' no default constraint to drop'
EXEC (@cmd) 
GO
IF EXISTS (SELECT 1 FROM syscolumns WHERE id = object_id('UserInfo') AND name = 'Name')
BEGIN
	ALTER TABLE UserInfo ALTER COLUMN [Name] NVARCHAR(125) NOT NULL 
	ALTER TABLE [dbo].[UserInfo] ADD DEFAULT (N'') FOR [Name]
END
GO

--新增视图
IF  EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_UserInfo]'))
	DROP VIEW [dbo].[V_UserInfo]
GO
CREATE VIEW [dbo].[V_UserInfo]
AS
	SELECT Name,Phone FROM [dbo].[UserInfo] WHERE Name='李娜'
GO
--查询视图
SELECT * FROM [dbo].[V_UserInfo]

--新建作业时，判断作业是否存在，存在则删除
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'作业名称')
	EXEC msdb.dbo.sp_delete_job @job_name=N'作业名称', @delete_unused_schedule=1
GO

--查询某个表被哪些存储过程调用
SELECT DISTINCT object_name(id) FROM syscomments WHERE id in
(SELECT id FROM sysobjects WHERE TYPE ='P') and TEXT like'%UserInfo%'
GO

--查看存储过程在哪些作业中被调用
SELECT * FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N'%P_I_UserInfo%'
GO

--查询某个字段在哪些存储过程中
SELECT NAME FROM sysobjects o, syscomments s  
WHERE o.id = s.id  
AND text LIKE '%Phone%'  
AND o.xtype = 'P'
GO

--创建索引时判断索引是否存在，存在则删除
IF  EXISTS(SELECT * FROM sysindexes WHERE id=object_id('UserInfo') and name='NonClusteredIndex_Id_Phone')
BEGIN
    DROP INDEX NonClusteredIndex_Id_Phone on [dbo].[UserInfo]
END
GO
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Id_Phone] ON [dbo].[UserInfo]
(
	[Id] ASC,
	[Phone] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO