Create view By cursor

Create view By cursor

now my manager ask me to make views in database ((A)) from all tables already exist  in database ((B)) that’s hard task to catch tha tabel one by one and make create view XXXX as select * from Table_name . this operation very hard and will take a long time . so my friend MR/Mohamed Osman make Script dynamic to make this Operation .

Run this script on the database (B) where there are tables and the database you want to make the views select from there put the name of this DB in prameter name @DBNAME

— =============================================
— Create View dynamic
— Created by Mohamed osman
— =============================================
DECLARE @DBNAME AS NVARCHAR(100)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar = CHAR(13) + CHAR(10)
SET @DBNAME =’GEN2010.DBO.’ —— views will select from this database
DECLARE @RESULT AS NVARCHAR (MAX)

DECLARE V CURSOR
READ_ONLY
FOR SELECT  ‘Create View ‘ + name + ‘ AS SELECT * FROM  ‘ + @DBNAME +  name
FROM sys.objects AS so WHERE so.type = ‘U’
DECLARE @name varchar(MAX)
OPEN V

FETCH NEXT FROM V INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

DECLARE @message varchar(max)
SELECT @message =  @name+ @NewLineChar +’GO’
PRINT @message
END
FETCH NEXT FROM V INTO @name
END

CLOSE V
DEALLOCATE V
GO