Copy Views from QA to PROD programmatically in SQL server

6gpjuf90 于 2023-04-04 发布在 SQL Server

关注(0)|答案(1)|浏览(128)

I am trying to get views from QA to PROD with scripts.

I am already looking into options like INFORMATION_SCHEMA.VIEWS or sql_modules , but one of the issue for those tables is that the view I have containing comment, and those two options returns code in one line, and the code got issue because the code become commented out. Ex:

Select *  -- test comment
from t1 -- test comment1
join t2 on t2.id = t1.id  -- test comment2

to

Select *  -- test comment from t1 -- test comment1 join t2 on t2.id = t1.id  -- test comment2

I also tried sp_helptext to get code in txt format in multiple lines, but the problem of this options is for some of the views, it returns code in half like this, and I am not able to run it

Select *  fr
om t1 joi
n t2 on t2.id = t1.id

Right now, I am simply using SSMS options. Right click on views -> Script Views as -> Create Or Alter to -> New Query Window Is there a way to run the above steps in code instead of SSMS?

sql-server

来源：https://stackoverflow.com/questions/75920911/copy-views-from-qa-to-prod-programmatically-in-sql-server

1条答案

按热度按时间

cu6pst1q1#

You can use sys.sql_modules to get code for a view, and put it into variable, and then execute it in your new database. Something like this untested code:

declare @sql nvarchar(max);
select @sql = definition
from anotherdb.sys.sql_modules
where object_id = object_id('anotherdb..someview');
exec(@sql);

To get multiple view, use a cursor or a loop to fetch many view names

赞(0）回复(0）举报 2023-04-04

我来回答

Copy Views from QA to PROD programmatically in SQL server

1条答案

相关问题

热门标签

最新问答