Sql-server – Create View For All Table Names Listed In Table

dynamic-sqlsql-server-2008-r2view

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to iterate each table name and create a view with the data from each of those table names. Like below is what I need to do, BUT my issue is that I iterate each table name and the view is dropped, not appended.

Declare @employee varchar(100), @sql varchar(4000), @location varchar(300)

select RTRIM(LTRIM(name)) As employee
INTO #TempTable1
from alphawhiskeycharlie
where name LIKE 'AL%'
AND (terminationdate IS NULL
OR CAST(terminationdate As Date) >= GetDate()-90)
ORDER BY RTRIM(LTRIM(name)) ASC

Declare c1 Cursor For

Select employee
FROM #TempTable1

Open c1 

Fetch Next From c1 Into @employee

Set @location = (Select fulltablelocation from servername.databasename.dbo.tableinformation where employeename = @employee)

While @@FETCH_STATUS = 0
  Begin
    Set @sql = 'IF OBJECT_ID('TestView', 'V') IS NOT NULL '
                + 'DROP VIEW TestView '
                + 'Create View [TestView] As '
                +'Select '''+@employee+''' As ''employee'', '
                +'COUNT(employeeID) As [TotalEmployees] '
                +'From '+@location+' '
                +'where employeename is not null'
    Print(@sql)
    exec(@sql)

    Fetch Next From c1 Into @employee

End

Close c1

Best Answer

I'm having trouble wrapping my head around exactly what your view is trying to do, but you might not even need a cursor for that. As an example:

USE tempdb;
GO

/*
CREATE TABLE Table1 ( employeeID BIT )
CREATE TABLE Table2 ( employeeID BIT )
CREATE TABLE Table3 ( employeeID BIT )
 */

IF ( OBJECT_ID( 'tempdb.dbo.#t_TempTable1' ) IS NULL )
BEGIN
    --DROP TABLE #t_TempTable1;
    CREATE TABLE #t_TempTable1
    (
        Employee                NVARCHAR( 128 ),
        FullTableLoc            NVARCHAR( 128 )
    );

    INSERT INTO #t_TempTable1 ( Employee, FullTableLoc )
                SELECT  'A', '[dbo].[Table1]'
    UNION ALL   SELECT  'B', '[dbo].[Table2]'
    UNION ALL   SELECT  'C', '[dbo].[Table3]';
END;
GO

DECLARE @CreateViewStatement    NVARCHAR( MAX ) = '';
SELECT  @CreateViewStatement = @CreateViewStatement + '
    UNION ALL
    SELECT  [Employee] = ''' + Employee + ''', [TotalEmployees] = COUNT( employeeID )
    FROM    ' + FullTableLoc 
FROM    dbo.#t_TempTable1;
    SET @CreateViewStatement = '
ALTER VIEW [dbo].[TestView]
AS' + SUBSTRING( @CreateViewStatement, 
        CHARINDEX( 'SELECT', @CreateViewStatement ) - 1, 
        LEN( @CreateViewStatement ) ) + ';';

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'TestView'
                    AND type = 'V' )
BEGIN
    EXEC( 'CREATE VIEW dbo.TestView AS SELECT TOP 0 x = 0;' );
END;

PRINT   @CreateViewStatement;
EXECUTE dbo.sp_executesql @statement = @CreateViewStatement;
Related Question