• Hi,

    I am trying to figure out how to change the first <TD> to add BGCOLOR in this script:

    declare @body varchar(max)

    -- Create the body

    set @body = cast( (

    select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )

    from (

    select dbtable = object_name( object_id ),

    entities = count( distinct name ),

    rows = count( * )

    from sys.columns

    group by object_name( object_id )

    ) as d

    for xml path( 'tr' ), type ) as varchar(max) )

    set @body = '<table cellpadding="2" cellspacing="2" border="1">'

    + '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'

    + replace( replace( @body, '<', '<' ), '>', '>' )

    + '<table>'

    print @body


  • Since nobody else is providing a solution I'll give it a try. I am simplifying the query against sys.columns because I don't really understand what you are doing. That said, you can use my example as a template and rearrange the query that feeds the table accordingly.

    WITH x1(xxx) AS


    SELECT TOP(10) td1 = name, td2 = column_id, td3 = max_length

    FROM sys.columns

    FOR xml path('tr'),ROOT('table')







    REPLACE(REPLACE(REPLACE(REPLACE(xxx,'<td1>','<td bgcolor="red">'),'td2','td'),'td3','td'),'</td1>','</td>'),

    '<table>','<table cellpadding="2" cellspacing="2" border="1">'

    ) AS XML


    FROM x1;

    Edit: code formatting issue.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • XML attributes can be embedded using [@attrname] = .. syntax with FOR XML


    (select [@bgcolor]='red', object_name( object_id ) for xml path ('td'), type),

    (select count( distinct name ) for xml path ('td'), type),

    td = count( * )

    from sys.columns

    group by object_name( object_id )

    for xml path('tr'), type

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply