• For those of you that would like a single procedure that doesn't depend upon the Extended Properties and will work for a single table.

    Here's what we use:

    /****** Object: StoredProcedure [Admin].[CreateMerge] Script Date: 9/3/2015 10:07:29 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*

    =============================================

    Author: Jason L. Selburg

    Create date: 07/25/2014

    Description: Returns the data for the program income receivables report

    - The 'Created','LastEdited','IsTiedToBL','TS' and GUID columns are ignored

    - @ExcludeData is set to 1, then only the merge statement is created with a single row with placeholders

    - @ReverseOrder and @TopCount are used when the data in the table exceeds the output length

    - @ReverseOrder orders the data by the PK DESC

    - @Topcount only return this number of rows. ONLY used when @ReverseOrder is set

    - @CustomWhere to hold a custom where claues for tables with large amounts of data

    */

    ALTER PROCEDURE [Admin].[CreateMerge]

    (@TableName NVARCHAR(256)

    ,@Schemaname NVARCHAR(256) = N'dbo'

    ,@ExcludeData INT = 0

    ,@ReverseOrder INT = 0

    ,@TopCount INT = 10

    ,@CustomWhere NVARCHAR(4000) = ''

    )

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE

    @i INT

    ,@sqlTextFirst VARCHAR(MAX)

    ,@sqlTextSecond VARCHAR(MAX)

    ,@justDataList VARCHAR(MAX)

    ,@EXECText NVARCHAR(MAX)

    ,@MergeText VARCHAR(MAX)

    ,@Filename VARCHAR(256)

    ,@Debug INT = 0;

    IF LEFT(@CustomWhere,6) <> 'WHERE ' AND LEN(@CustomWhere) > 1

    BEGIN

    SELECT 'Invalid WHERE Clause'

    RETURN

    END;

    IF OBJECT_ID('tempdb..#DataToMerge') IS NOT NULL

    DROP TABLE #DataToMerge

    CREATE TABLE #DataToMerge

    (nDex INT IDENTITY

    ,UsingList NVARCHAR(MAX));

    WITH TableInfoStart

    AS (SELECT DISTINCT

    t.object_id

    ,s.name AS schemaName

    ,t.name AS tableName

    ,icc.name AS PK

    ,icc.is_identity AS PKisID

    ,STUFF((SELECT

    ',[' + c.Name + ']' AS [text()]

    FROM

    sys.COLUMNS AS c

    WHERE

    t.OBJECT_ID = c.OBJECT_ID

    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')

    AND c.NAME NOT LIKE '%GUID'

    FOR

    XML PATH('')

    ),1,1,'') AS columnlist

    ,'''' + CHAR(10) + ' ,('' + ' + STUFF((SELECT

    CASE WHEN st.precision = 0 OR

    st.user_type_id IN (40,41,42,43,8,11,241,58,61,189,98) THEN ---- needs wrapped in quotes

    ' + '','' + ISNULL('''''''' + REPLACE([' + c.Name + '],'''''''','''''''''''') + '''''''',''NULL'')'

    ELSE

    ' + '','' + ISNULL(REPLACE(CAST([' + c.Name + '] AS VARCHAR(MAX)),'''''''',''''''''''''),''NULL'')'

    END AS [text()]

    FROM

    sys.COLUMNS AS c

    INNER JOIN sys.types AS st

    ON c.user_type_id = st.user_type_id

    WHERE

    t.OBJECT_ID = c.OBJECT_ID

    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')

    AND c.NAME NOT LIKE '%GUID'

    AND ( (c.name <> icc.name AND icc.is_identity = 1) OR icc.is_identity = 0)

    FOR

    XML PATH('')

    ),1,8,'') + ' + '')''' AS selectlist

    ,CHAR(10) + ' (' + STUFF((SELECT

    CASE WHEN st.precision = 0 OR

    st.user_type_id IN (40,41,42,43,8,11,241,58,61,189,98) THEN ---- needs wrapped in quotes

    ','''+ c.Name +''''

    ELSE

    ','+ c.Name

    END AS [text()]

    FROM

    sys.COLUMNS AS c

    INNER JOIN sys.types AS st

    ON c.user_type_id = st.user_type_id

    WHERE

    t.OBJECT_ID = c.OBJECT_ID

    AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')

    AND c.NAME NOT LIKE '%GUID'

    FOR

    XML PATH(''))

    ,1,1,'') + ') ---- Place your data here' AS justDatalist

    ,STUFF(STUFF((SELECT

    ' AND [TGT].[' + col.COLUMN_NAME + '] = ' + '[SRC].[' + col.COLUMN_NAME + ']' + CHAR(10) AS [text()]

    FROM

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab

    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col

    ON Col.Constraint_Name = Tab.Constraint_Name

    AND Col.Table_Name = Tab.Table_Name

    AND Col.CONSTRAINT_SCHEMA = Tab.TABLE_SCHEMA

    WHERE

    tab.CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND tab.TABLE_SCHEMA = s.NAME

    AND tab.TABLE_NAME = t.name

    FOR

    XML PATH('')

    ),1,8,''),1,1,'') AS JoinClause

    FROM

    sys.tables AS t

    INNER JOIN sys.schemas AS s

    ON t.schema_id = s.schema_id

    INNER JOIN sys.indexes AS i

    ON i.object_id = t.object_id

    AND i.is_primary_key = 1

    INNER JOIN sys.index_columns AS ic

    ON i.object_id = ic.object_id

    AND i.index_id = ic.index_id

    INNER JOIN sys.columns AS icc

    ON ic.column_id = icc.column_id

    AND ic.object_id = icc.object_id

    WHERE

    s.name = @SchemaName

    AND

    t.name = @TableName)

    ,TableInfo

    AS (SELECT

    object_id

    ,schemaName

    ,tableName

    ,PK

    ,PKisID

    ,CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END AS columnList

    ,CASE WHEN PKisID=1 THEN REPLACE(selectlist,'[' + PK + '],','') ELSE selectlist END AS selectlist

    ,CASE WHEN PKisID=1 THEN REPLACE(justDatalist,',(' + PK + ',','(') ELSE justDatalist END AS justDatalist

    ,JoinClause

    FROM TableInfoStart)

    SELECT

    @FileName = 'C:\SupportingData\' + schemaName + '.' + tableName + '.sql'

    ,@sqlTextFirst =

    '/*' + CHAR(10) +

    schemaName + '.' + tableName + CHAR(10) +

    '*/' + CHAR(10) + CHAR(10) +

    ' MERGE INTO [' + schemaName + '].[' + tableName + '] AS [TGT]' + CHAR(10) +

    ' USING (VALUES '

    ,@sqlTextSecond = CHAR(10) +

    ' ) AS [SRC] ' + CHAR(10) +

    ' (' + columnList + ')' + CHAR(10) +

    ' ON' + JoinClause + CHAR(10) +

    ' ---- When the record does not exist... INSERT' + CHAR(10) +

    ' WHEN NOT MATCHED BY TARGET THEN' + CHAR(10) +

    ' INSERT ' + CHAR(10) +

    ---- handle the PK being an identitiy

    ' (' + CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END + ') ' + CHAR(10) +

    ' VALUES ' + CHAR(10) +

    ---- handle the PK being an identitiy

    ' (' + CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END + ');' + CHAR(10) +

    'GO' + CHAR(10)

    ,@justDataList = justDataList

    ,@EXECText = CHAR(10) + +

    CASE WHEN @TopCount = 1 THEN 'SELECT TOP (' + CAST(@TopCount AS VARCHAR(9)) + ') ' ELSE 'SELECT ' END

    + ISNULL(selectList,'') + CHAR(10) +

    'FROM [' + schemaName + '].[' + tableName + '] ' + ISNULL(@CustomWhere,'') + ' ORDER BY ' + ISNULL(PK,'1') +

    CASE WHEN @ReverseOrder = 1 THEN ' DESC' ELSE ' ASC' END

    FROM TableInfo AS s;

    PRINT @exectext

    IF @ExcludeData = 0

    BEGIN

    INSERT #DataToMerge

    EXEC sp_executesql @EXECText

    IF EXISTS (SELECT NULL FROM #DataToMerge AS dtm)

    BEGIN

    SELECT REPLACE(REPLACE(REPLACE(REPLACE(@sqlTextFirst +

    STUFF(STUFF((SELECT

    UsingList AS [text()]

    FROM

    #DataToMerge

    FOR XML PATH(''))

    ,1,1,''), 12,1,CHAR(10) + ' ')+

    @sqlTextSecond

    ,'USING (VALUES (','USING (VALUES ('), '<', '<'), '>', '>'), '&', '&')

    END

    ELSE

    SELECT 'No Data'

    END

    ELSE

    BEGIN

    SELECT

    REPLACE(@sqlTextFirst

    + @JustDataList

    + @sqlTextSecond ,'USING (VALUES (','USING (VALUES (')

    END;

    END

    GO

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg