1
Create view xxx
as
Select * from
DBName.dbo.table1

I have an above mentioned view where DBName is dynamic. How can I create this view? I know we can't use dynamic variables in a view. I wanted to know how it can be done with the help of a function.

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
RAJ
  • 57
  • 7
  • It must be a view? – Mihail Stancescu Nov 04 '16 at 06:51
  • Yes. It must be a view.I was thinking of calling the output of select * from in a view through a table valued function. In function we can use dynamic variables if i am right – RAJ Nov 04 '16 at 06:58
  • Unfortunately you can't use dynamic sql in a function, more reference [here](http://stackoverflow.com/questions/9607935/call-dynamic-sql-from-function). – Mihail Stancescu Nov 04 '16 at 07:00
  • lets create view in every potential database. There is something wrong in basic idea of view – Jacek Cz Nov 04 '16 at 07:38
  • Why not create the view in database with name `DBName`, and select from it like `SELECT*FROM DBName.dbo.xxx;`? – TT. Nov 04 '16 at 07:45

3 Answers3

0

Use can use synonyms here just pass a DbName as variable like this

Declare @Dbname varchar(255)
Declare @qry varchar(255)

Set @Dbname="Name of the dataBase"

Set @qry='Create synonym ABC for'+@DbName+'.dbo.table1'
Exec (@qry)

Now use this 

IF EXISTS (SELECT 1 from sysobjects where id=object_id(''SN_DWTableCustomer'') and xtype=''SN'')
BEGIN 
DROP VIEW xxx 
Create view xxx as Select * from ABC 
END
ELSE 
BEGIN 
Create view xxx as Select * from ABC
END

For better understanding use the below links https://www.techonthenet.com/oracle/synonyms.php What is the use of SYNONYM in SQL Server 2008?

Please reply.

Community
  • 1
  • 1
Rohit Gupta
  • 455
  • 4
  • 16
  • But what if DBName changes. I again have to create the view by running above mentioned code.Is there any other solution? – RAJ Nov 04 '16 at 06:53
  • Simply drop the view and again recreate it or you want view in another table. – Rohit Gupta Nov 04 '16 at 07:31
0
DECLARE @DBname varchar(max) = 'newdb'
DECLARE @sql varchar(max) = 'create view MyView as select * from '+@DBname+'.dbo.test'

EXEC (@sql)

SELECT * FROM MyView
User
  • 804
  • 8
  • 22
  • But what if DBName changes. I again have to create the view by running above mentioned code.Is there any other solution? – RAJ Nov 04 '16 at 06:54
  • Then you can create Stored procedure with above first 3 lines with the parameter @DBName and execute the sp. Please give more details if this solution not suits your requirement – User Nov 04 '16 at 06:54
  • I cant use SP. I was thinking of calling the output of select * from in a view through a table valued function. In function we can use dynamic variables if i am right – RAJ Nov 04 '16 at 06:59
0
DECLARE @DB VARCHAR(100) 
SET @DB = 'Your_DBName' DECLARE @CreateViewStatement  NVARCHAR(MAX) 
SET @CreateViewStatement = '      USE '+ QUOTENAME(@DB) +';      
EXEC(''
         CREATE VIEW [dbo].[MyTable] AS
         SELECT * FROM Your_Table
  '') '
EXEC (@CreateViewStatement)
Mansoor
  • 4,061
  • 1
  • 17
  • 27