The purpose of this code /script is to use the available system tables to build views programmatically based on all the BASE tables in a given database. These views may be a stepping stone for building other view on top of them. I am sure one may find another way of looping through the list of tables without using cursors. The point of this exercise is to demonstrate how quickly all tables may be used in creating views for.
use [AdventureWorks2012] go -- we will have to have a prefix otherwise the server will issue an error for existing obj -- I have chosen the 'tbl_' instead of 'UV_', it is totally up to you what to choose declare @Prefix nvarchar(10) set @Prefix = 'tbl_' -- 4 local variable for each record on a table 4 part naming convention declare @TABLE_CATALOG nvarchar(64), @TABLE_SCHEMA nvarchar(64), @TABLE_NAME nvarchar(64) -- The string declaration in here is for the TSQL statement that will be executed to create each view. 2000, may be an over-kill but this is up to you too declare @ExecString nvarchar(2000) set @ExecString = '' -- This sample shows that there were 71 table name, not a big of a deal for a cursor and will not "crash" your system.. I know how you feel about cursor 😉 declare MyTables cursor for select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'base table' order by table_name --open the newly created cursor and grab the first record. open MyTables fetch next from MyTables into @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME -- start of the loop process and will continue until no more records in there to process while @@FETCH_STATUS = 0 begin set @ExecString += 'CREATE VIEW [' + @TABLE_SCHEMA + '].['+@Prefix+@TABLE_NAME + '] AS SELECT [' + @TABLE_SCHEMA + '].[' + @Table_name + '].* FROM [' + @TABLE_SCHEMA + '].[' + @Table_name +']' exec(@execstring) fetch next from MyTables into @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME set @ExecString = '' end -- wrap it up by destroying the objects close MyTables deallocate MyTables
Hope you will find a use for some of the pieces in here and happy computing. Please feel free commenting, forwarding your concerns, etc.