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.
Again, this may not be practical to some of us at all, but, it is for training purposes.
Using the Microsoft tutorial database AdventureWorks, in this case 2012 version, we are going to read all the table names from the system object INFORMATION_SCHEMA.TABLES. Since this module / object contains various types of TABLES therefore we will use a where clause to grab the "base table" only. This way we are not going to get the views and other user defined table type objects.
So, the statement to get the list of tables as shown in the code in below; select table_name from INFORMATION_SCHEMA.Tables where table_type = 'base table' is being used in cursor declaration statement. Of course we would need to declare couple of local variables for the data read and those are shown in below as well.
The next step is the looping through the table names. The loop process then executes a TSQL statement for each create view. The server name (may be omitted), catalog name, schema name and the table name are being used for execution of each statement. And, of course the values for this variables come from the information_schema.tables object as stated in above.
Concatenating the 3, 4 pieces as shown in the code and executing that string using Exec() function / command will get the job done. It's very simple and effective. You may modify the string or the T-SQL to add additional SQL query hints, table hints, etc to suit your needs.
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 MyTablesHope you will find a use for some of the pieces in here and happy computing. Please feel free commenting, forwarding your concerns, etc.
