Skip to main content
deleted 1 character in body
Source Link
Hannah Vernon
  • 70.5k
  • 22
  • 177
  • 321

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regulard"Regular expressions" search-and-replace functionality included in SQL Server Management Studio. Once you get the hang of how this works, it can become something you use almost every day.

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regulard expressions" search-and-replace functionality included in SQL Server Management Studio. Once you get the hang of how this works, it can become something you use almost every day.

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regular expressions" search-and-replace functionality included in SQL Server Management Studio. Once you get the hang of how this works, it can become something you use almost every day.

deleted 2 characters in body
Source Link
Hannah Vernon
  • 70.5k
  • 22
  • 177
  • 321

enter image description hereenter image description here

I'm using the dbo.spt_fallback_usg table as an example. After I drag-and-drop them into the query window, the columns are:

xserver_name, xdttm_ins, xdttm_last_ins_upd, xfallback_vstart, dbid, segmap, lstart, sizepg, vstart

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box. This puts Pressing the "Replace All" button will put each column name on a new line. Next, modify the "Search and Replace" items like this:

enter image description here

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box. This puts each column name on a new line. Next, modify the "Search and Replace" items like this:

enter image description here

I'm using the dbo.spt_fallback_usg table as an example. After I drag-and-drop them into the query window, the columns are:

xserver_name, xdttm_ins, xdttm_last_ins_upd, xfallback_vstart, dbid, segmap, lstart, sizepg, vstart

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box. Pressing the "Replace All" button will put each column name on a new line. Next, modify the "Search and Replace" items like this:

Source Link
Hannah Vernon
  • 70.5k
  • 22
  • 177
  • 321

The following is a bastardized way of doing this, but is useful since it shows how you can use the very powerful "Regulard expressions" search-and-replace functionality included in SQL Server Management Studio. Once you get the hang of how this works, it can become something you use almost every day.

"Drag-and-drop" the list of columns from the Object Explorer window onto a Query Window, as shown:

enter image description here

Hit [CTRL + H] to open the "Search and Replace" dialog box:

enter image description here

In the example above, I've entered , * into the "Find What" text box, and \n into the "Replace With" text box. This puts each column name on a new line. Next, modify the "Search and Replace" items like this:

enter image description here

This will turn each individual column name into a CASE WHEN... statement.

Now replace the "newlines" with +, as in:

enter image description here

This results in the following code in the query window:

CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END + 
CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END + 
CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END + 
CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END + 
CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END + 
CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END + 
CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END + 
CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END + 
CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END + 

This can then be manually wrapped into a SELECT statement like:

SELECT 
    CASE WHEN xserver_name IS NULL THEN '' ELSE 'xserver_name: ' + [xserver_name] END + 
    CASE WHEN xdttm_ins IS NULL THEN '' ELSE 'xdttm_ins: ' + [xdttm_ins] END + 
    CASE WHEN xdttm_last_ins_upd IS NULL THEN '' ELSE 'xdttm_last_ins_upd: ' + [xdttm_last_ins_upd] END + 
    CASE WHEN xfallback_vstart IS NULL THEN '' ELSE 'xfallback_vstart: ' + [xfallback_vstart] END + 
    CASE WHEN dbid IS NULL THEN '' ELSE 'dbid: ' + [dbid] END + 
    CASE WHEN segmap IS NULL THEN '' ELSE 'segmap: ' + [segmap] END + 
    CASE WHEN lstart IS NULL THEN '' ELSE 'lstart: ' + [lstart] END + 
    CASE WHEN sizepg IS NULL THEN '' ELSE 'sizepg: ' + [sizepg] END + 
    CASE WHEN vstart IS NULL THEN '' ELSE 'vstart: ' + [vstart] END 
FROM dbo.spt_fallback_usg