Adding columns to existing query

• mysscacc

  Old Hand

  Points: 318

  More actions

  November 23, 2015 at 4:39 am

  #73655

  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

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  November 23, 2015 at 7:34 am

  #1841721

  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/%5B/url%5D

  _______________________________________________________________

  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/