April 1, 2015 at 1:20 pm
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
April 1, 2015 at 8:18 pm
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')
)
SELECT
CAST
(
REPLACE
(
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.
-- Itzik Ben-Gan 2001
April 2, 2015 at 5:58 am
XML attributes can be embedded using [@attrname] = .. syntax with FOR XML
select
(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