Generate Column Metadata

/****************************************************************

This procedure will print to the SSMS messages window a
formatted list of column names for tables, procedures, and
views that can be cut and pasted for creating other scripts 
and procedures. 

Options include adding brackets if that's your thing, pulling
actual sample data from the table/view specified, or you can
create your own custom query.

Format types are shown right below. At the very bottom I've
included examples of how to use the procedure. Below that I've
added scripts for creating a test table, test view, and a test
procedure. Run those once, then highlight all of the examples
(all at once if you want to) and you'll get the idea.

I'm sure I haven't covered every possible format someone 
might want nor will some of the options do anything on every
format type. Also, performance was not really an issue here
since it seems plenty fast enough for something that is just 
an ad hoc utility.

Please feel free to take this ball and run with
it. Just post to the forum any ideas or improvements so
everyone can benefit.

Regards
Steven Willis
19 January 2013

****************************************************************/

CREATE PROCEDURE [dbo].[GetFormattedNames]

     @strObjSchema VARCHAR(50)
    ,@strObjName VARCHAR(50)
    ,@strObjType VARCHAR(50) 
    ,@strFormat VARCHAR(50)
    ,@isBracketed BIT = 0
    ,@isGetSampleData BIT = 0
    ,@isUseQuery BIT = 0 
    ,@strQuery NVARCHAR(MAX) = NULL
    
    /*
    ObjTypes: 
        TABLE
        VIEW
        PROCEDURE
        
    Table/View Formats: 
        NAME
        VARIABLE
        VARIABLE_AS_COLS
        VARIABLE_EQ_COLS
        VARIABLE_DATATYPE_WITH_DEFAULTS
        DECLARE
        DECLARE_NULLABLE
        UPDATE
        MERGE_UPDATE
        TEST_PARAMETERS_UNPOPULATED
        TEST_PARAMETERS_POPULATED    

    Procedure Formats: 
        NAME
        VARIABLE
        DECLARE
        TEST_PARAMETERS_UNPOPULATED
        TEST_PARAMETERS_POPULATED    

    */    
AS
BEGIN

    SET NOCOUNT ON

    DECLARE
         @strDelimiter VARCHAR(10)
        ,@strDataType VARCHAR(50)
        ,@strDataLength VARCHAR(50)
        ,@strLineBreak VARCHAR(10)
        ,@strCols VARCHAR(MAX)
        ,@strSQL VARCHAR(MAX)
        ,@strBL VARCHAR(1)
        ,@strBR VARCHAR(1)
        ,@strCount NVARCHAR(MAX)
        ,@strColName NVARCHAR(100)
        ,@intTempVar INT
        ,@x INT


    IF OBJECT_ID('tempdb..##TempTable') IS NOT NULL
        DROP TABLE ##TempTable        

    CREATE TABLE ##TempTable (
        [ID] INT NOT NULL,
        [OBJ_NAME] NVARCHAR(50) NULL,
        [DATA_TYPE] NVARCHAR(50) NULL,
        [DATA_LENGTH] NVARCHAR(50) NULL,
        [COLUMN_DEFAULT] NVARCHAR(50) NULL,
        [IS_NULLABLE] NVARCHAR(50) NULL,
        [IS_IDENTITY] BIT NULL,
        [COLUMN_VALUE] NVARCHAR(MAX),
        PRIMARY KEY (ID))


    IF @isBracketed = 1
        BEGIN
            SET @strBL = '['
            SET @strBR = ']'
        END
    ELSE
        BEGIN
            SET @strBL = ''
            SET @strBR = ''
        END

    IF @isUseQuery <> 1
        SET @isUseQuery = 0
        
    IF NULLIF(@strQuery,'') IS NULL
        SET @isUseQuery = 0
        
    SET @strDelimiter = ','
    SET @strLineBreak = CHAR(13) + CHAR(10)


    /* If populate with query option has been chosen validate the query. */        
    SET @strCount = ''
    SET @x = 0
    
    IF @isUseQuery = 1
        BEGIN TRY
        
            SET @strCount = N'SELECT @intTempVar = (SELECT COUNT(*) FROM ('+@strQuery+') AS r)'
            EXEC sp_executesql @strCount, N'@intTempVar INT OUTPUT', @x OUTPUT     
                
        END TRY
        BEGIN CATCH
            RETURN    --invalid query
        END CATCH

    
    /* Populate a temp table with the query results */    
    IF @isUseQuery = 1
        BEGIN

            SELECT
                @x = COUNT(*)
            FROM
                INFORMATION_SCHEMA.VIEWS
            WHERE
                TABLE_SCHEMA = 'dbo'
                AND TABLE_NAME = 'vwQueryResults'

            IF @x > 0
                DROP VIEW dbo.vwQueryResults    

            SET @strQuery = N'CREATE VIEW dbo.vwQueryResults AS '+@strQuery
            EXEC (@strQuery)
            
            SET @strObjSchema = 'dbo'
            SET @strObjName = 'vwQueryResults'
            SET @strObjType = 'VIEW'
            
        END

            
    IF @strObjType IN ('TABLE','VIEW')
        BEGIN

            SET @strSQL = '
            INSERT INTO ##TempTable
            SELECT 
                iscol.ORDINAL_POSITION AS ID
               ,iscol.COLUMN_NAME
               ,iscol.DATA_TYPE
               ,(CASE
                    WHEN (iscol.DATA_TYPE IN (''char'',''varchar'',''nchar'',''nvarchar'')
                         AND iscol.CHARACTER_OCTET_LENGTH = -1)
                        THEN ''(MAX)''
                    WHEN iscol.DATA_TYPE IN (''char'',''varchar'')  
                        THEN ''(''+CAST(iscol.CHARACTER_OCTET_LENGTH AS VARCHAR)+'')''
                    WHEN iscol.DATA_TYPE IN (''nchar'',''nvarchar'') 
                        THEN ''(''+CAST(iscol.CHARACTER_OCTET_LENGTH/2 AS VARCHAR)+'')''
                    WHEN iscol.DATA_TYPE = ''decimal'' 
                        THEN ''(''+CAST(iscol.NUMERIC_PRECISION AS VARCHAR)+'',''+CAST(iscol.NUMERIC_SCALE AS VARCHAR)+'')''
                    ELSE ''''
                END) AS DATA_LENGTH   
               ,iscol.COLUMN_DEFAULT    
               ,(CASE
                    WHEN iscol.IS_NULLABLE = ''YES'' THEN ''NULL''
                    ELSE ''NOT NULL''
                END) AS IS_NULLABLE
               ,COLUMNPROPERTY(OBJECT_ID('''+@strObjSchema+'.'+@strObjName+'''),iscol.COLUMN_NAME,''IsIdentity'') AS IS_IDENTITY
               ,NULL AS COLUMN_VALUE
            FROM
                INFORMATION_SCHEMA.COLUMNS AS iscol
            WHERE
                iscol.TABLE_SCHEMA = '''+@strObjSchema+'''
                AND iscol.TABLE_NAME = '''+@strObjName+'''
            ORDER BY
                iscol.ORDINAL_POSITION'

        END
    ELSE IF @strObjType = 'PROCEDURE'
        BEGIN

            SET @strSQL = '
            INSERT INTO ##TempTable
            SELECT 
                iscol.ORDINAL_POSITION AS ID
               ,iscol.PARAMETER_NAME
               ,iscol.DATA_TYPE
               ,(CASE
                    WHEN (iscol.DATA_TYPE IN (''char'',''varchar'',''nchar'',''nvarchar'')
                         AND iscol.CHARACTER_OCTET_LENGTH = -1)
                        THEN ''(MAX)''
                    WHEN iscol.DATA_TYPE IN (''char'',''varchar'')  
                        THEN ''(''+CAST(iscol.CHARACTER_OCTET_LENGTH AS VARCHAR)+'')''
                    WHEN iscol.DATA_TYPE IN (''nchar'',''nvarchar'') 
                        THEN ''(''+CAST(iscol.CHARACTER_OCTET_LENGTH/2 AS VARCHAR)+'')''
                    WHEN iscol.DATA_TYPE = ''decimal'' 
                        THEN ''(''+CAST(iscol.NUMERIC_PRECISION AS VARCHAR)+'',''+CAST(iscol.NUMERIC_SCALE AS VARCHAR)+'')''
                    ELSE ''''
                END) AS DATA_LENGTH 
               ,NULL AS COLUMN_DEFAULT
               ,NULL AS IS_NULLABLE
               ,NULL AS IS_IDENTITY
               ,NULL AS COLUMN_VALUE
            FROM
                INFORMATION_SCHEMA.PARAMETERS AS iscol
            WHERE
                iscol.SPECIFIC_SCHEMA = '''+@strObjSchema+'''
                AND iscol.SPECIFIC_NAME = '''+@strObjName+'''
            ORDER BY
                iscol.ORDINAL_POSITION'
        END
    ELSE
        BEGIN
            PRINT 'INVALID OBJECT TYPE'
            RETURN
        END
                

    /* Populate the temp table */
    EXEC (@strSQL)


    /* Get some sample values */
    IF @isGetSampleData = 1 AND @strObjType IN ('TABLE','VIEW')
        BEGIN
        
            DECLARE UpdateList CURSOR
            FOR
                SELECT
                    ID    
                   ,OBJ_NAME
                FROM
                    ##TempTable
                ORDER BY
                    ID

                
            OPEN UpdateList
            FETCH NEXT FROM UpdateList INTO 
                 @x
                ,@strColName

            WHILE @@FETCH_STATUS = 0 
                BEGIN

                    SET @strSQL = 
                        'UPDATE ##TempTable 
                         SET COLUMN_VALUE =
                         (
                         SELECT TOP(1) '+@strColName+' 
                         FROM '+@strObjSchema+'.'+@strObjName+'
                         )
                         WHERE ID = '+CAST(@x AS VARCHAR(3))
                         
                    EXEC (@strSQL)    
                
                    FETCH NEXT FROM UpdateList INTO 
                         @x
                        ,@strColName

                END

            CLOSE UpdateList
            DEALLOCATE UpdateList

        END    
    
    
    /* Rearrange the data based on specified format */
    IF @strFormat IN 
        (
         'TEST_PARAMETERS_UNPOPULATED'
        ,'TEST_PARAMETERS_POPULATED'
        )
        BEGIN
            UPDATE ##TempTable
            SET OBJ_NAME = ''
        END        

    IF @strObjType IN ('TABLE','VIEW')
        BEGIN

            IF @strFormat IN 
                (
                 'VARIABLE'
                ,'VARIABLE_AS_COLS'
                ,'VARIABLE_EQ_COLS'
                ,'VARIABLE_DATATYPE_WITH_DEFAULTS'
                ,'DECLARE'
                ,'DECLARE_NULLABLE'
                )
                SET @strDelimiter = ',@'
                
            SELECT 
                @strCols = COALESCE(@strCols + @strLineBreak + @strDelimiter,'')
                + 
                CASE 
                    WHEN @strFormat IN 
                            (
                             'VARIABLE'
                            ,'VARIABLE_DATATYPE_WITH_DEFAULTS'
                            ,'DECLARE'
                            ,'DECLARE_NULLABLE'
                            ,'TEST_PARAMETERS_POPULATED'
                            ,'TEST_PARAMETERS_UNPOPULATED'
                            )
                        THEN CAST(tt.OBJ_NAME AS NVARCHAR(255))
                    WHEN @strFormat IN ('VARIABLE_AS_COLS')
                        THEN CAST(tt.OBJ_NAME AS NVARCHAR(255))
                            +' AS '+@strBL+CAST(tt.OBJ_NAME AS NVARCHAR(255))+@strBR
                    WHEN @strFormat IN ('VARIABLE_EQ_COLS')
                        THEN CAST(tt.OBJ_NAME AS NVARCHAR(255))
                            +' = '+@strBL+CAST(tt.OBJ_NAME AS NVARCHAR(255))+@strBR
                    WHEN @strFormat IN ('UPDATE')
                        THEN @strBL+CAST(tt.OBJ_NAME AS NVARCHAR(255))+@strBR
                            +' = @'+CAST(tt.OBJ_NAME AS NVARCHAR(255))
                    WHEN @strFormat IN ('MERGE_UPDATE')
                        THEN @strBL+'Target'+@strBR+'.'+@strBL+CAST(tt.OBJ_NAME AS NVARCHAR(255))+@strBR
                            +' = @'+CAST(tt.OBJ_NAME AS NVARCHAR(255))
                    ELSE
                        @strBL+CAST(tt.OBJ_NAME AS NVARCHAR(255))+@strBR
                END
                +
                CASE 
                    WHEN @strFormat IN 
                        (
                         'DECLARE'
                        ,'VARIABLE_DATATYPE_WITH_DEFAULTS'
                        )
                        THEN ' '+@strBL+UPPER(CAST(tt.DATA_TYPE AS NVARCHAR(255)))+@strBR
                            +UPPER(CAST(tt.DATA_LENGTH AS NVARCHAR(255)))
                            +
                            CASE
                                WHEN @strFormat IN ('DECLARE')
                                    AND tt.IS_IDENTITY = 1
                                    THEN ' IDENTITY(1,1)'
                                ELSE ''
                            END
                    WHEN @strFormat IN ('DECLARE_NULLABLE')
                        THEN ' '+@strBL+UPPER(CAST(tt.DATA_TYPE AS NVARCHAR(255)))+@strBR
                            +UPPER(CAST(tt.DATA_LENGTH AS NVARCHAR(255)))
                            +
                            CASE
                                WHEN tt.IS_IDENTITY = 1
                                    THEN ' IDENTITY(1,1)'
                                ELSE ''
                            END
                            +' '+UPPER(CAST(tt.IS_NULLABLE AS NVARCHAR(255)))
                    ELSE ''
                END
                +
                CASE 
                    WHEN @strFormat = 'VARIABLE_DATATYPE_WITH_DEFAULTS' 
                        AND (tt.DATA_TYPE IN ('CHAR','VARCHAR','NCHAR','NVARCHAR'))
                        THEN ' = ' +
                            CASE 
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE ''''''
                            END
                    WHEN @strFormat = 'VARIABLE_DATATYPE_WITH_DEFAULTS' 
                        AND tt.DATA_TYPE = 'DATETIME'
                        THEN ' = ' +
                            CASE 
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE 'NULL'
                            END
                    WHEN @strFormat = 'VARIABLE_DATATYPE_WITH_DEFAULTS' 
                        THEN ' = ' +
                            CASE 
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE '0'
                            END
                    WHEN @strFormat = 'TEST_PARAMETERS_UNPOPULATED' 
                        AND (tt.DATA_TYPE IN ('CHAR','VARCHAR','NCHAR','NVARCHAR'))
                        THEN ''''''
                    WHEN @strFormat = 'TEST_PARAMETERS_UNPOPULATED' 
                        AND tt.DATA_TYPE = 'DATETIME'
                        THEN 'NULL'
                    WHEN @strFormat = 'TEST_PARAMETERS_UNPOPULATED' 
                        THEN '0'
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND (tt.DATA_TYPE IN ('CHAR','VARCHAR','NCHAR','NVARCHAR'))
                        THEN '''' +
                            CASE 
                                WHEN @isGetSampleData = 1 
                                    THEN tt.COLUMN_VALUE
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL 
                                    THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE 'AbcXyz'
                            END
                        + ''''
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND tt.DATA_TYPE = 'DATETIME'
                        THEN '''' +
                            CASE 
                                WHEN @isGetSampleData = 1 
                                    THEN CONVERT(VARCHAR(19), CAST(tt.COLUMN_VALUE AS DATETIME), 120)
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL 
                                    THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE CONVERT(VARCHAR(19), CAST(GETDATE() AS DATETIME), 120)
                            END 
                         + ''''
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND tt.DATA_TYPE = 'BIT'
                        THEN 
                            CASE 
                                WHEN @isGetSampleData = 1 
                                    THEN tt.COLUMN_VALUE                            
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL 
                                    THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE '0'
                            END                
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND tt.DATA_TYPE = 'UNIQUEIDENTIFIER'
                        THEN 
                            CASE 
                                WHEN @isGetSampleData = 1 
                                    THEN tt.COLUMN_VALUE                            
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL 
                                    THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE ''''+(CAST(NEWID() AS VARCHAR(36)))+''''
                            END    
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        THEN 
                            CASE 
                                WHEN @isGetSampleData = 1 
                                    THEN tt.COLUMN_VALUE                            
                                WHEN tt.COLUMN_DEFAULT IS NOT NULL 
                                    THEN UPPER(tt.COLUMN_DEFAULT)
                                ELSE '123'
                            END            
                    ELSE ''
                END    
            FROM
                ##TempTable AS tt
                
        END
    ELSE IF @strObjType = 'PROCEDURE'
        BEGIN
        
            SELECT 
                @strCols = COALESCE(@strCols + @strLineBreak + @strDelimiter,'')
                + 
                CASE 
                    WHEN @strFormat IN 
                            (
                             'VARIABLE'
                            ,'DECLARE'
                            ,'TEST_PARAMETERS_POPULATED'
                            ,'TEST_PARAMETERS_UNPOPULATED'
                            )
                        THEN CAST(tt.OBJ_NAME AS NVARCHAR(255))
                    ELSE
                        @strBL+REPLACE(CAST(tt.OBJ_NAME AS NVARCHAR(255)),'@','')+@strBR
                END
                +
                CASE 
                    WHEN @strFormat IN ('DECLARE')
                        THEN ' '+@strBL+UPPER(CAST(tt.DATA_TYPE AS NVARCHAR(255)))+@strBR
                            +UPPER(CAST(tt.DATA_LENGTH AS NVARCHAR(255)))
                    ELSE ''
                END
                +
                CASE 
                    WHEN @strFormat = 'TEST_PARAMETERS_UNPOPULATED' 
                        AND (tt.DATA_TYPE IN ('CHAR','VARCHAR','NCHAR','NVARCHAR'))
                        THEN ''''''
                    WHEN @strFormat = 'TEST_PARAMETERS_UNPOPULATED' 
                        AND tt.DATA_TYPE = 'DATETIME'
                        THEN 'NULL'
                    WHEN @strFormat = 'TEST_PARAMETERS_UNPOPULATED' 
                        THEN '0'
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND (tt.DATA_TYPE IN ('CHAR','VARCHAR','NCHAR','NVARCHAR'))
                        THEN '''AbcXyz'''
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND tt.DATA_TYPE = 'DATETIME'
                        THEN ''''+(CONVERT(VARCHAR(19), CAST(GETDATE() AS DATETIME), 120))+''''
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND tt.DATA_TYPE = 'BIT'
                        THEN '0'
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        AND tt.DATA_TYPE = 'UNIQUEIDENTIFIER'
                        THEN  ''''+(CAST(NEWID() AS VARCHAR(36)))+''''
                    WHEN @strFormat = 'TEST_PARAMETERS_POPULATED' 
                        THEN '123'
                    ELSE ''
                END    
            FROM
                ##TempTable AS tt

        END


    /* Print the results */    
    PRINT @strObjType+' '+@strObjSchema+'.'+@strObjName
    PRINT 'Format: '+@strFormat
    
    SET @strCols = REPLACE(@strCols,'(GETDATE())','GETDATE()')
    SET @strCols = REPLACE(REPLACE(@strCols,'((',''),'))','')
        
    IF @strObjType IN ('TABLE','VIEW')
        AND @strFormat IN 
            (
             'VARIABLE'
            ,'VARIABLE_AS_COLS'
            ,'VARIABLE_EQ_COLS'
            ,'VARIABLE_DATATYPE_WITH_DEFAULTS'
            ,'DECLARE'
            ,'DECLARE_NULLABLE'
            )
        PRINT ' @'+@strCols+@strLineBreak+@strLineBreak
    ELSE
        PRINT ' '+@strCols+@strLineBreak+@strLineBreak


    /* Clean up */    
    SELECT
        @x = COUNT(*)
    FROM
        INFORMATION_SCHEMA.VIEWS
    WHERE
        TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'vwQueryResults'

    IF @x > 0
        DROP VIEW dbo.vwQueryResults        

    IF OBJECT_ID('tempdb..##TempTable') IS NOT NULL
        DROP TABLE ##TempTable  
        
        

    /*
    Examples:
    
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','NAME',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','VARIABLE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','VARIABLE_AS_COLS',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','VARIABLE_EQ_COLS',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','VARIABLE_DATATYPE_WITH_DEFAULTS',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','DECLARE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','DECLARE_NULLABLE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','UPDATE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','MERGE_UPDATE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_UNPOPULATED',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',1,0,0,NULL

    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','NAME',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','VARIABLE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','VARIABLE_AS_COLS',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','VARIABLE_EQ_COLS',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','VARIABLE_DATATYPE_WITH_DEFAULTS',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','DECLARE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','DECLARE_NULLABLE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','UPDATE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','MERGE_UPDATE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','TEST_PARAMETERS_UNPOPULATED',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','vwTestContacts','VIEW','TEST_PARAMETERS_POPULATED',1,0,0,NULL

    EXEC dbo.GetFormattedNames 'dbo','TestAddContact','PROCEDURE','NAME',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestAddContact','PROCEDURE','VARIABLE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestAddContact','PROCEDURE','DECLARE',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestAddContact','PROCEDURE','TEST_PARAMETERS_UNPOPULATED',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestAddContact','PROCEDURE','TEST_PARAMETERS_POPULATED',1,0,0,NULL

    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',0,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',1,0,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',0,1,0,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',0,0,1,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',1,0,1,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',0,1,1,NULL
    EXEC dbo.GetFormattedNames 'dbo','TestContact','TABLE','TEST_PARAMETERS_POPULATED',0,1,1
        ,'SELECT FName, LName, isActive, dateCreated, sortOrder FROM dbo.TestContact'
    
    
    */
    /*
    --For testing only

    CREATE TABLE [dbo].[TestContact](
        [ContactID] [int] IDENTITY(1,1) NOT NULL,
        [FName] [nvarchar](50) NULL,
        [LName] [nvarchar](50) NULL,
        [Address1] [nvarchar](50) NULL,
        [Address2] [nvarchar](50) NULL,
        [City] [nvarchar](50) NULL,
        [State] [nvarchar](50) NULL,
        [Zip] [nvarchar](50) NULL,
        [Phone] [nvarchar](50) NULL,
        [Email] [nvarchar](50) NULL,
        [isActive] [bit] NOT NULL,
        [dateCreated] [datetime] NOT NULL,
        [sortOrder] [int] NOT NULL,
     CONSTRAINT [PK_TestContact] PRIMARY KEY CLUSTERED 
    (
        [ContactID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[TestContact] ADD  CONSTRAINT [DF_isActive]  DEFAULT ((0)) FOR [isActive]
    GO

    ALTER TABLE [dbo].[TestContact] ADD  CONSTRAINT [DF_dateCreated]  DEFAULT (GETDATE()) FOR [dateCreated]
    GO

    ALTER TABLE [dbo].[TestContact] ADD  CONSTRAINT [DF_sortOrder]  DEFAULT ((9999)) FOR [sortOrder]
    GO


    CREATE VIEW [dbo].[vwTestContacts]
    AS
    SELECT
        FName
       ,LName
       ,Address1
       ,Address2
       ,City
       ,State
       ,Zip
    FROM
        dbo.TestContact
    GO



    CREATE PROCEDURE [dbo].[TestAddContact]

         @FName NVARCHAR(50)
        ,@LName NVARCHAR(50)
        ,@Address1 NVARCHAR(50)
        ,@Address2 NVARCHAR(50)
        ,@City NVARCHAR(50)
        ,@State NVARCHAR(50)
        ,@Zip NVARCHAR(50)
        ,@Phone NVARCHAR(50)
        ,@Email NVARCHAR(50)
        ,@isActive BIT
        ,@dateCreated DATETIME
        ,@sortOrder INT

    AS 
    BEGIN

        DECLARE @ContactID INT

        INSERT INTO dbo.TestContact
            (
             FName
            ,LName
            ,Address1
            ,Address2
            ,City
            ,State
            ,Zip
            ,Phone
            ,Email
            ,isActive
            ,dateCreated
            ,sortOrder
            )
        VALUES
            (
             @FName
            ,@LName
            ,@Address1
            ,@Address2
            ,@City
            ,@State
            ,@Zip
            ,@Phone
            ,@Email
            ,@isActive
            ,GETDATE()
            ,@sortOrder
            )

         SELECT
            @ContactID = SCOPE_IDENTITY()

    END
    GO


    -- Create a test entry 
    
    EXEC [dbo].[TestAddContact]
         'George'
        ,'Washington'
        ,'123 Apple Blossom Lane'
        ,NULL
        ,'Mt. Vernon'
        ,'VA'
        ,'21345'
        ,'555-567-2345'
        ,'test@aol.com'
        ,1
        ,'2013-01-18 18:54:28.203'
        ,100
    GO
    
    */
END