November 23, 2015 at 4:39 am
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>'
EXEC msdb.dbo.sp_send_dbmail @recipients='<ABCD@gmail.com>',
@subject = 'DB List',
@body = @body,
@body_format = 'HTML',
@profile_name = 'ProfileName';
How do I tweak the above code for this sql
select col1,col2,col3,col4,col5,col6,col7,col8 from table1
I do not have any subquery.
Thanks
November 23, 2015 at 7:34 am
smmkr17 (11/23/2015)
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>'
EXEC msdb.dbo.sp_send_dbmail @recipients='<ABCD@gmail.com>',
@subject = 'DB List',
@body = @body,
@body_format = 'HTML',
@profile_name = 'ProfileName';
How do I tweak the above code for this sql
select col1,col2,col3,col4,col5,col6,col7,col8 from table1
I do not have any subquery.
Thanks
Hi and welcome to the forums. You seem to have forgotten to post any details about what you are trying to do. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply