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

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

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.

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

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 }}