What do you do in SQL Server to CREATE OR ALTER?

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'PROCEDURE')
EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')


CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS BEGIN
-- body
END

For triggers, you have to lean on the proprietary system views.

Is this the most accepted convention in the meantime?

EDIT: As n8wrl suggested, the official word suggests that this feature is not a high priority. Hence the question.

98633 次浏览
小开

Looks like it's a while off: link text

typical script for me:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ig_InsertDealer' AND type = 'P')
DROP PROC dbo.ig_InsertDealer
GO
CREATE PROCEDURE dbo.ig_InsertDealer
...
GO
GRANT EXECUTE ON dbo.ig_InsertDealer TO ...
GO
小开

That's basically the way to do it, yes. I just wonder if you have a particular reason to use the "EXEC" approach:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')

Why not just:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE dbo.SynchronizeRemoteCatalog

???

For triggers, there's sys.triggers. Those are system catalog views in the "sys" schema - not strictly or directly tables, really.

Marc

小开

I'll use either depending on context: my initial-build or major refactoring scripts will use check/drop/create, pure maintenance scripts use alter.

小开

I'd use OBJECT_ID(...) IS NOT NULL before a DROP.

Object identifiers have to be unique, so it works without using system tables:

CREATE TRIGGER dbo.ExistingTable ON dbo.AnotherTable FOR UPDATE
AS
SET NOCOUNT ON
GO

gives

Msg 2714, Level 16, State 2, Procedure MetaClass, Line 3
There is already an object named ExistingTable ' in the database.

I normally use ALTER because of how we work with source control, etc.

小开

Every time a developer writes IF EXISTS(...) DROP a seal pup is clubbed. You should know exactly what's in the database and your upgrade script should do the CREATE or ALTER as appropriate, based on the current version of your application schema: Version Control and your Database.

小开

I have a template, which allows to execute a script several times without errors.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[aaa_test]') AND type in (N'P', N'PC'))
EXEC('CREATE PROCEDURE aaa_test AS')
EXEC('GRANT EXECUTE ON aaa_test TO someone')
GO


ALTER PROCEDURE aaa_test
@PAR1 INT,
@PAR2 INT=0
AS
BEGIN
SELECT @PAR1 AS Par1, CASE @PAR2 WHEN 0 THEN 'Default' ELSE 'Other' END AS Par2
END
GO

Execution:

EXEC aaa_test 1
EXEC aaa_test 1,5
小开

This article makes a good point about losing permissions when dropping an object in SQL server.

So here is the approach which retains permissions:

IF OBJECT_ID('spCallSomething') IS NULL
EXEC('CREATE PROCEDURE spCallSomething AS SET NOCOUNT ON;')
GO


ALTER PROCEDURE spCallSomething ...
--instead of DROP/CREATE

Also works for functions, just replace PROCEDURE with FUNCTION in the above code.

Another reason to consider doing it this way is tolerance to failure. Suppose your DROP succeeds, but your CREATE fails - you end with a broken DB. Using ALTER approach, you will end up with an older version of the object.

小开

We encountered a situation where we needed to update a remote site, but we didn’t have DROP permissions. Until now, we have been using the ‘DROP and CREATE’ script built into SSMS 2008 R2, but now we needed to change. We created three templates, which we drop above the appropriate ALTER scripts when we need to update a stored procedure or function:

—- Stored Procedure
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE PROCEDURE [dbo].[<Name_Of_Routine, , >] AS SET NOCOUNT ON;')
EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;')
GO


—- Scalar Function
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS INT AS BEGIN RETURN 0 END;')
EXEC('GRANT EXECUTE ON [<Name_Of_Routine, , >] TO Public AS dbo;')
GO


—- Table-based Function
IF OBJECT_ID('[dbo].[<Name_Of_Routine, , >]') IS NULL
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Routine, , >] (@i INT) RETURNS @O TABLE(i INT) AS BEGIN INSERT INTO @O SELECT 0 RETURN END;')
GO

Any special permissions get scripted after each CREATE (Table functions cannot be assigned permissions). After that, the ALTER doesn’t change it, and if they add or modify the permissions, they remain. Doing it this way, it’s an easy task to copy the name of the function or stored procedure, and use the Template Parameter replacement to automating the completion of these scriptlets.

Now, I’m hoping that the good folks at Microsoft will either add this to their “Script ___ as” lists, or give us the ability to create our own such that this scripting comes ‘baked-in’

You may want to throw some weight behind the SQL Server feedback entry at: https://connect.microsoft.com/SQLServer/feedback/details/344991/create-or-alter-statement. It seems to be one of the few that are still accessible publicly, and they state that they "have started a feasibility review for this to decide if we can ship this in the near future." The more voices, the more likely this will happen!

(Update: now also using the following code for Triggers and Views) 

-- Triggers
IF OBJECT_ID('[dbo].[<Name_Of_Trigger, , >]') IS NULL -- Check if Trigger Exists
EXEC('CREATE TRIGGER [dbo].[<Name_Of_Trigger, , >] ON [<Name_Of_Table, , >] AFTER UPDATE AS SET NOCOUNT ON;') -- Create dummy/empty SP
GO


-- Views
IF OBJECT_ID('[dbo].[<Name_Of_View, , >]') IS NULL -- Check if View Exists
EXEC('CREATE VIEW [dbo].[<Name_Of_View, , >] AS SELECT 1;') -- Create dummy/empty View
GO
小开

You should not drop an object. Dropping an object suffers from two problems:

1) If the CREATE fails, you no longer have an object. (You can use transactions to avoid that, at the expense of a lot of boilerplate code)

2) You lose permissions on the object, if you do not explicitly re-create them.

I prefer to create a blank object within an "if not exists" condition, and then use ALTER, and have written helper procedures for that purpose.

小开

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE.

The year is 2016 and it does now have DIE (Drop If Exists) in SQL Server 2016 RTM and CREATE OR ALTER (introduced in 2016 SP1).

Taking Drop If Exists first the caveats around needing to re-apply permissions with this approach still apply. Example syntax is

DROP PROCEDURE IF EXISTS dbo.SynchronizeRemoteCatalog


GO


CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS
BEGIN
BODY:
END


GO


/*TODO: Reapply permissions*/

CREATE OR ALTER retains the permissions. Example syntax is

 CREATE OR ALTER PROCEDURE dbo.SynchronizeRemoteCatalog
AS
BEGIN
BODY:
END

The corresponding MSSQL Tiger Team blog post explains

CREATE OR ALTER can be used in programmability objects such as:

  • STORED PROCEDURES (including natively compiled)
  • FUNCTIONS (Transact-SQL, including natively compiled)
  • TRIGGERS
  • VIEWS

But cannot be used in:

  • Objects that require storage (tables, indexes and indexed views)
  • CLR user-defined functions
  • Deprecated programmability objects (RULE and DEFAULT)
  • Non-programmability objects (such as CREATE ASSEMBLY, CREATE TABLE or CREATE - SCHEMA). On these objects, the syntax for CREATE and ALTER is very different from a syntax and usability perspective.
小开

I always alter my objects because a drop is really bad practice and can leave your DB in an bad state if an object fails to create (24/7 db!), as well as what the other posters have mentioned about nuking permissions.

Editors like Sublime, Atom, and VS Code will let you make code snippets as templates for these to quickly gen-up your skeleton script. SQL 2016 now finally supports DROP IF EXISTS construct, but it still approaches from the wrong direction - that everything is a drop/create instead of a one time create in the distant past and alter from then on. Also, I have tried to make my headers as short as will possibly work, so I don't get any fancier than create proc dbo.myproc as as the create stub.

Views:

if objectproperty(object_id('dbo.myview'), 'IsView') is null begin
exec('create view dbo.myview as select 1 c')
end
go
alter view dbo.myview as
-- select *
-- from table
go

Procs:

if objectproperty(object_id('dbo.myproc'), 'IsProcedure') is null begin
exec('create proc dbo.myproc as')
end
go
alter procedure dbo.myproc as
set nocount on
-- Add the stored proc contents here...
go

UDF (scalar):

if objectproperty(object_id('dbo.myudf'), 'IsScalarFunction') is null begin
exec('create function dbo.myudf returns int as begin return null end')
end
go
alter function dbo.myudf(@s varchar(100)) returns int as
begin
-- return len(@s)
end
go

UDF (tabular):

if objectproperty(object_id('dbo.myudf'), 'IsTableFunction') is null begin
exec('create function dbo.myudf returns @t table(x int) as begin return end')
end
go
alter function dbo.myudf(@s varchar(100))
returns @result table (
-- Columns returned by the function
id int identity(1, 1) primary key not null
,result varchar(100) null
)
begin
return
end
go
小开

The year is 2017 and SQL Server has CREATE OR ALTER

SQL Server 2016 SP1 and SQL Server vNext have new T-SQL language statement – CREATE [OR ALTER] for:

  • STOREDPROCEDURES
  • FUNCTIONS
  • TRIGGERS
  • VIEWS

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

小开

I prefer CREATE-ALTER approach (not syntax) over DROP-CREATE for two reasons:

  • permissions (with DROP-CREATE you have to recreate them)
  • object_id (altering object won't change it)

Example DROP-CREATE:

--Initial creation:
CREATE PROCEDURE dbo.my_proc
AS
SELECT *
FROM dbo.a
WHERE i < 10;
GO


SELECT OBJECT_ID('dbo.my_proc');
GO




-- Recreating
DROP PROCEDURE IF EXISTS dbo.my_proc;
GO


CREATE PROCEDURE dbo.my_proc
AS
-- some meaningless comment
SELECT *
FROM dbo.a
WHERE i < 10;
GO


SELECT OBJECT_ID('dbo.my_proc');
GO

DB Fiddle

As we can see the object_id has changed.

Example 2: CREATE-ALTER

-- Initial creation
CREATE PROCEDURE dbo.my_proc2
AS
SELECT *
FROM dbo.a
WHERE i < 10;
GO


SELECT OBJECT_ID('dbo.my_proc2');
GO


-- Altering
CREATE OR ALTER PROCEDURE dbo.my_proc2
AS
-- some meaningless comment
SELECT *
FROM dbo.a
WHERE i < 10;
GO


SELECT OBJECT_ID('dbo.my_proc2');
GO

DB Fiddle

In this scenario the object_id remains the same.

Sample scenario when this can cause some problems. Let's assume that we use SQL Server 2016 Query Store and force to use specific query plan for stored procedure.

DROP-CREATE

USE T1;
GO
-- make sure that Query Store is READ_WRITE
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[a](
[i] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[g] [uniqueidentifier] NULL,
[z] VARCHAR(10)
);
END
GO


-- populate table (15k records)
INSERT INTO dbo.a(g, z)
SELECT NEWID(), number
FROM (SELECT CAST([key] AS INT) AS number
FROM OPENJSON( '[1' + REPLICATE(',1',3000-1)+']')
) AS num
GO 5


-- initial creation
CREATE PROCEDURE dbo.my_proc
AS
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO


-- Clustered Index Scan
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;


SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
GO
--dc1


-- creating index
CREATE NONCLUSTERED INDEX IX_dbo_a_z
ON dbo.a([z] ASC) INCLUDE ([i], [g]);
GO


-- index seek
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;


SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;


-- forcing plan GUI, clustered scan
-- dc3


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- dc4


-- Clustered Index Scan
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- dc5


/* MAIN PART  - DROP - RECREATE */
DROP PROCEDURE IF EXISTS dbo.my_proc;
GO


CREATE PROCEDURE dbo.my_proc
AS
-- some meaningless comment added by developer
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO


/* MAIN PART END */


-- Index Seek
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- object_id in query store is NULL
-- is_forced_plan flag is ignored !!!

First execution:
DC1

Adding index and execute: enter image description here

Forcing plan: enter image description here enter image description here

Another execution: enter image description here

After DROP-CREATE: enter image description here

CREATE - ALTER

USE T2;
GO
-- make sure that Query Store is READ_WRITE
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[a](
[i] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[g] [uniqueidentifier] NULL,
[z] VARCHAR(10)
);
END
GO


-- populate table (15k records)
INSERT INTO dbo.a(g, z)
SELECT NEWID(), number
FROM (SELECT CAST([key] AS INT) AS number
FROM OPENJSON( '[1' + REPLICATE(',1',3000-1)+']')
) AS num
GO 5


-- initial creation
CREATE PROCEDURE dbo.my_proc
AS
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO


-- Clustered Index Scan
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
-- ca1
GO


-- creating index
CREATE NONCLUSTERED INDEX IX_dbo_a_z
ON dbo.a([z] ASC) INCLUDE ([i], [g]);
GO


-- index seek
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
--ca2


-- forcing plan GUI
--ca3


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
--ca4


-- Clustered Index Scan
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;
--ca5
GO


/* MAIN PART  - CREATE-ALTER */
CREATE OR ALTER PROCEDURE dbo.my_proc
AS
-- some meaningless comment added by developer
SELECT *
FROM dbo.a
WHERE z LIKE '12%'
AND 1 = (SELECT 1);
GO


/* MAIN PART END */


-- Clustered Index Scan
EXEC dbo.my_proc;


EXEC sp_query_store_flush_db;
SELECT qsq.query_id,
qsq.query_text_id,
qsq.context_settings_id,
qsq.[object_id],
OBJECT_NAME(qsq.[object_id]) AS [object_name],
qsp.is_forced_plan,
qsqt.query_sql_text,
qsrs.count_executions,
CAST(qsp.query_plan AS XbML) AS sql_query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt
ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp
ON qsq.query_id= qsp.query_id
JOIN sys.query_store_runtime_stats qsrs
ON qsrs.plan_id = qsp.plan_id
WHERE query_sql_text LIKE '%dbo.a%'
AND qsq.[object_id] <> 0
ORDER BY qsq.query_id;


-- is_forced_plan is valid

First execution:
enter image description here

Adding index and execute: enter image description here

Forcing plan: enter image description here enter image description here

Another execution: enter image description here

After CREATE-ALTER: enter image description here

Result

With Drop-Create we lost forced plan.

小开

Just to my extend previous answer.

Another reason why I prefer CREATE-ALTER over DROP-CREATE approach. It could lead to losing specifc properties about object. For example ExecIsStartup:

USE master
GO


CREATE TABLE dbo.silly_logging(id INT IDENTITY(1,1) PRIMARY KEY
,created_date DATETIME DEFAULT GETDATE()
,comment VARCHAR(100));
GO


CREATE PROCEDURE dbo.my_procedure
AS
INSERT INTO dbo.silly_logging(comment)
VALUES ('SQL Server Startup');
GO


-- mark procedure to start at SQL Server instance startup
EXEC sp_procoption @ProcName = 'dbo.my_procedure'
, @OptionName = 'startup'
, @OptionValue = 'on';




SELECT name, create_date, modify_date, is_auto_executed
FROM master.sys.procedures
WHERE is_auto_executed = 1;
--name  create_date modify_date is_auto_executed
--my_procedure  2017-07-28 06:36:21.743 2017-07-28 06:36:24.513 1

Now let's assume that someone wants to update this procedure using DROP-CREATE:

DROP PROCEDURE dbo.my_procedure;
GO


CREATE PROCEDURE dbo.my_procedure
AS
-- adding meaningless comment
INSERT INTO dbo.silly_logging(comment)
VALUES ('SQL Server Startup');
GO


SELECT name, create_date, modify_date, is_auto_executed
FROM master.sys.procedures
WHERE is_auto_executed = 1;
-- empty

And if you are not aware of it or you don't check you will end up with procedure that won't start.


提交答案