Geek Answers Handbook

How to change table schema name in all stored procedures

I know how to change the table schema on SQL Server 2005:

ALTER SCHEMA NewSchama TRANSFER dbo.Table1

But how can I check and / or modify stored procedures using the name of the old schema?

Sorry: I mean: There are stored procedures that have the old table schema name in the sql of the stored procedure ... How can I edit all stored procedures that have dbo.Table1 in the procedure body ...

+5
source share
4 answers

, , , , . , , - , SQL- .

script , , sprocs, , script .

0
  • > SSMS, Create Proc.
  • " " (Alt - H), "" ""
  • F R 'dbo.Table1' 'dbo.Table2'
  • (F5), SP.

, .

+1

DECLARE @SearchObject VARCHAR (100)

SET @SearchObject = 'searchable_table_name' - 'searchable_table_name' ,

SELECT sc.name [ ], so.name [ ],
CASE so.xtype "U" THEN "" 'P' THEN ' ' WHEN 'F' THEN ' ' ELSE 'Other' [ ]

FROM sysobjects,

INNER JOIN syscolumns sc ON so.id = sc.id

WHERE sc.name LIKE '%' + @SearchObject + '%' AND so.xtype IN ('U', 'P', 'F') - U: , P: , F: (udf)

ORDER BY [ ] ASC

- , .

, , '% from' + @SearchObject + '%'

( id sysobjects type = 'P' name = '')

- (. )

- Exec sp_helptext 'DeleteAssetByID'

0

, Reports, dbo, , Reports, -, Reporting:

CREATE SCHEMA Reporting

script, dbo Reporting:

ALTER SCHEMA Reporting TRANSFER dbo.Reports

:

ALTER SCHEMA 'newSchema' TRANSFER 'oldSchema'.'table'
0

More articles:


All Articles