SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

What a View

By John Esraelo,

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]


-- 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
    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

-- start of the loop process and will continue until no more records in there to process
while @@FETCH_STATUS = 0


  set @ExecString += 'CREATE VIEW [' + @TABLE_SCHEMA + '].['+@Prefix+@TABLE_NAME + '] AS SELECT [' +  @TABLE_SCHEMA + '].[' +  @Table_name + '].* FROM [' + @TABLE_SCHEMA + '].[' +  @Table_name +']'

  fetch next from MyTables into
  set @ExecString = ''


-- 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. 

Total article views: 7700 | Views in the last 30 days: 1
Related Articles

Could not complete cursor operation because the table schema changed after the cursor was declared => Dynamics AX

Could not complete cursor operation because the table schema changed after the cursor was declared =...





Cursors and variables

Using variables within a cursor declaration


An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server

An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server