Sql server- Does renaming a table name have any bad effects?

user_8830634 0
Viewed 3351

I know this is a broad question but there doesn't seem to be resources online to answer my doubt.

I noticed if I rename a table in sql server, it would prompt a message saying it might affect or break the roots of the tables.

What does that mean?

Does it actually break the stored procedure or script ?

ssms tsql sql-server sql
3 Answers

Sure. It will break the routines which are referring it. You can use your management studio to find objects that are referencing it:

enter image description here

or the following script:

DECLARE @SearchWord NVARCHAR(128) = 'table'

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%'+@SearchWord+'%' 
UNION
SELECT OBJECT_NAME(id) 
FROM SYSCOMMENTS 
WHERE [text] LIKE '%'+@SearchWord+'%' 
GROUP BY OBJECT_NAME(id)
UNION 
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%'+@SearchWord+'%' 

RETURN

SELECT *
FROM sys.dm_sql_referenced_entities ('dbo.table', 'OBJECT')

SELECT *
FROM sys.dm_sql_referencing_entities ('dbo.table', 'OBJECT')

Also, it will be best if you look for your server side files (asp, .net) and check if the table is used in SSIS packages or in reporting files (.rdl).

user_10803540

You might want to consider the existence of the SCHEMABINDING option for Views and UDFs. These allow you to ensure that the underlying tables/views upon which the View or UDF depends aren't changed (such as by being renamed).

But note that a) it's an option that's off by default and b) it's not available for stored procedures, which go completely in the other direction by having deferred name resolution.

So yes, Views, UDFs and Stored Procedure definitions can all be invalidated by renaming a table, and you can only protect the first two by employing SCHEMABINDING.

user_154980

Here is an image and some code that shows for example your views will break if you dont know what effects your changed column has.

Create view

enter image description here

Edit column name and select from view enter image description here

Code

   /****** Script for SelectTopNRows command from SSMS  ******/

  /*Main table*/
 SELECT TOP 1000 [Person]
     ,[Book]
 FROM [LegOgSpass].[dbo].[Books]


 /*Create view*/
 create view dbo.booksview as

 select person,Book from dbo.Books

 /*Selcet from view*/
 Select * from dbo.booksview


 /*Change column name*/
 /****** Object:  Table [dbo].[Books]    Script Date: 03-11-2017 09:42:52 ******/
  DROP TABLE [dbo].[Books]
 GO

 /****** Object:  Table [dbo].[Books]    Script Date: 03-11-2017 09:42:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Books](
[PersonNEW] [nvarchar](50) NULL,
[Book] [nvarchar](50) NULL
) ON [PRIMARY]

GO

/*Select from view again with Person changed to PersonNew*/
Select * from dbo.booksview
user_34362710
Related