Need Help -- Views

Question

Need Help -- Views

sql2k8

Hall of Fame

Points: 3455
More actions
August 19, 2018 at 12:09 am

#361543

SQL Server 2014 Enterprise Edition 64 bit
Windows Server 2012 R2 Enterprise Edition 64 bit
There are two databases A and B. I want to create views in Database B Using Tables in Database A. I am able to generate the code for it. But I am manually execute the code in Database B. I am looking for the code itself to execute dynamically and create the views in database B. I want to use no lock on all of the tables(Database A) in the views in Database B. I want one view with no lock on each table. Can you please help.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

sql2k8 - Sunday, August 19, 2018 12:09 AM
SQL Server 2014 Enterprise Edition 64 bit
Windows Server 2012 R2 Enterprise Edition 64 bit
There are two databases A and B. I want to create views in Database B Using Tables in Database A. I am able to generate the code for it. But I am manually execute the code in Database B. I am looking for the code itself to execute dynamically and create the views in database B. I want to use no lock on all of the tables(Database A) in the views in Database B. I want one view with no lock on each table. Can you please help.

You will have to elaborate further on this, provide DDL, what you have tried etc. Database A-Z doesn't mean anything in this context!
😎

You have been around long enough to know that no one can answer this kind of question without further information.

sql2k8 Hall of Fame Points: 3455 More actions · Answer 2

DECLARE @SourceDB SYSNAME ;
DECLARE @TrgtDB SYSNAME

SET @SourceDB = 'DatabaseA'
SET @TrgtDB = 'DatabaseB'

DECLARE @CreateViewStatement NVARCHAR(MAX)
SET @CreateViewStatement = '
      USE '+ QUOTENAME(@TrgtDB) +';
      EXEC(''
             CREATE VIEW [dbo].[vw_TestTable] AS
             SELECT *
FROM [dbo].[TestTable] WITH (NOLOCK)
      '')

'
EXEC (@CreateViewStatement)
This is creating views but taking table in database b and create view in database b. Just one table itself. I want all of the tables in database A to be used in creating views in database B.

sql2k8 Hall of Fame Points: 3455 More actions · Answer 3

create table dbo.#QueryResults
(
QResults VARCHAR(MAX)
)

INSERT INTO dbo.#QueryResults(QResults)
select
'CREATE VIEW ' + 'vw_' + t.name + CHAR(13) + 'AS ' + CHAR(13) + 'SELECT * ' + CHAR(13) + 'FROM DatabaseA.' + s.name + '.[' + t.name +'] WITH (NOLOCK) ; ' + CHAR(13)
FROM sys.tables t
JOIN sys.schemas s
ON s.schema_id = t.schema_id
ORDER BY s.name, t.name ;

DECLARE @InsertString NVARCHAR(MAX) ;
DECLARE @QueryResults1 NVARCHAR(MAX) ;

set @QueryResults1 = 'SELECT * FROM dbo.#QueryResults'

set @InsertString = @QueryResults1

EXEC [DatabaseB].dbo.sp_executesql @InsertString ;

DROP TABLE dbo.#QueryResults ;

This one is creating the TSQL . I have to run it manually in database each view. I Cannot run the whole statement as a batch. If I do I am getting the below mentioned error message.

sql2k8 Hall of Fame Points: 3455 More actions · Answer 4

Msg 156, Level 15, State 1, Procedure vw_TestC, Line 2

Incorrect syntax near the keyword 'CREATE'.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Please defend your use of the NOLOCK hint. You do realize that this can be bad.

Joe Torre SSChampion Points: 10248 More actions · Answer 6

I would caution against using the WITH (NOLOCK) hint as your views could potentially return incorrect or inconsistent data.
I have a database Jic, I created another database JicViews:


USE JicViews
GO
DECLARE
   @sql          nvarchar(4000) = N'CREATE VIEW View_name AS SELECT * FROM Schema_table;'
 , @create_view  nvarchar(4000)
 , @schema_table nvarchar(200) ;

DECLARE t CURSOR FOR
SELECT
N'[Jic].' + QuoteName(Schema_Name(t.schema_id)) + '.' + QuoteName(t.name)
FROM
Jic.sys.tables t
WHERE
t.is_ms_shipped = 0 ;
OPEN t ;
FETCH t
INTO
@schema_table ;
WHILE @@fetch_Status = 0
BEGIN
SET @create_view =
   Replace(
Replace(
   Replace(
@sql
, 'View_name'
, 'vw' + Right(Replace(@schema_table, '].[', '_'), Len(Replace(@schema_table, '].[', '_'))))
   , 'Schema_table'
   , @schema_table)
, 'vw[Jic_'
, 'dbo.[vwJic_') ;
--PRINT @create_view
EXEC ( @create_view ) ;
FETCH t
INTO
   @schema_table ;
END ;
CLOSE t ;
DEALLOCATE t

sql2k8 Hall of Fame Points: 3455 More actions · Answer 7

Joe,
Thank You very much for the Help. It worked like a charm.

aaron.reese SSChampion Points: 13498 More actions · Answer 8

Why not create synonyms?

Synonyms are awesome because you can create them to have the same name in your target database as they are called in the source database and you don't have to edit the code as it moves through environments

Eg. you have been working on modifying a view which is now ready for production testing/release
In DEV you have a table TableX which actually lives in your database. views will use the native TableX
in UAT, TableX does not exist as a table object, instead it is a synonym that points to TableX in PROD. This way the UAT environment is using the live data, but running the logic from DEV
in PROD, you replace the existing view with the revised view and it now uses the native table in PROD.

Dev-Ops team are happy because the view that you deployed to live is EXACTLY THE SAME* as the view you checked in to source code control.
* not quite the same if you have WITH SCHEMABINDING as SCHEMABINDING can't work across databases.