Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

OBJECT_DEFINITION and sp_rename [Snippet]

DZone's Guide to

OBJECT_DEFINITION and sp_rename [Snippet]

View this short article that includes a code snippet of an interesting quirk of sp_rename.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Today, I found an interesting quirk of sp_rename: renaming a view, stored procedure, function, or trigger will not update the object's definition that is returned by the OBJECT_DEFINITION function. This is documented, but I think it might take people by surprise.

Example

So, if you first create the following view:

CREATE VIEW dbo.InitialViewName
AS
SELECT Id
FROM dbo.SampleTable

Then, you update its name:

EXEC sp_rename 'InitialViewName', 'UpdatedViewName'

Now, when you get the view's definition:

SELECT OBJECT_DEFINITION(OBJECT_ID('UpdatedViewName'));

The result might surprise you

CREATE VIEW dbo.InitialViewName  AS  SELECT        Id  FROM            dbo.SampleTable

Notice it's still InitialViewName. So, if you use the OBJECT_DEFINITION in your SQL scripts, you better stick to dropping and re-creating these objects.

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
database ,code snippet ,object_definition

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}