generate a comma seperated list of columns heading

January 20, 2018

alter proc xxx.usp_generate_column_name_string
@database varchar(100),@schema varchar(100), @table varchar(100)

as

declare @str varchar(max) = ‘

select *,”’+@database+”’ as database_name,”’+@schema+”’ as schema_name,”’+@table+”’ as table_name from (
select stuff((select ”,” + name as [text()]
from
(
select c.name from ‘ + @database + ‘.sys.tables a
inner join ‘ + @database + ‘.sys.schemas b on a.schema_id = a.schema_id
inner join ‘ + @database + ‘.sys.columns c on c.object_id= a.object_id
where b.name = ”’+@schema+”’ and a.name =”’+@table+”’) x

for xml path (””)),1,1,””) cols
) a


exec (@str)

go

exec xxx.usp_generate_column_name_string ‘test’ , ‘dbo’,’jl1_tmp’

Advertisements

Defrag Database Files

January 15, 2018

I was prevented from running CHECKDB in one of our largest databases: CheckDB fails with error: The operating system returned error 665

This is due to fragmentation of database files.

I checked the fragmentation using: https://docs.microsoft.com/en-us/sysinternals/downloads/contig

I could not get the 64 bit version to work but the 32 bit version works

Extract the exe to the c drive, not your users folder and open up a command prompt in that directory example c:\contig -a “file path” will tell you how fragmented your file is, in my case 1.7 million fragments c:\contig “file path” will defrag the file, you dont need to be offline to do this. You can check progress by opening up a new command prompt and repeating the command with the -a switch

Resizing filegroup files

January 7, 2018

I used the following to resize database files

Essentially what we do is create a new filegroup, move tables, indexes etc over drop the old filegroup, rename the new filegroup so it has the name of the old one.

create filegroup
create files
make the new filegroup the default for when you move the tables

script out indexes, filter on your filegroup if necersary, this also wont script columnstore indexes, check if it works for primary keys - it should

SELECT
DB_NAME() AS database_name,
sc.name + N'.' + t.name AS table_name,
(SELECT MAX(user_reads)
FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
last_user_update,
CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */'
ELSE
CASE is_primary_key WHEN 1 THEN
N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +
CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED '
ELSE N'CREATE ' +
CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +
CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' +
N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '
END +
/* key def */ N'(' + key_definition + N')' +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N' INCLUDE (' + include_definition + N')'
ELSE N''
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N' WHERE ' + filter_definition ELSE N''
END +
/* with clause - compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N' WITH (' +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END
ELSE N'' END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END
ELSE N'' END
+ N')'
ELSE N''
END +
/* ON where? filegroup? partition scheme? */
' ON ' + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N'')
ELSE psc.name + N' (' + partitioning_column.column_name + N')'
END
+ N';'
END AS index_create_statement,
si.index_id,
si.name AS index_name,
partition_sums.reserved_in_row_GB,
partition_sums.reserved_LOB_GB,
partition_sums.row_count,
stat.user_seeks,
stat.user_scans,
stat.user_lookups,
user_updates AS queries_that_modified,
partition_sums.partition_count,
si.allow_page_locks,
si.allow_row_locks,
si.is_hypothetical,
si.has_filter,
si.fill_factor,
si.is_unique,
ISNULL(pf.name, '/* Not partitioned */') AS partition_function,
ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
t.create_date AS table_created_date,
t.modify_date AS table_modify_date
into #t
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name)
FROM sys.index_columns AS ic
JOIN sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
ORDER BY table_name, si.index_id
OPTION (RECOMPILE);
GO

select * from #t where index_id !=0

make sure you have script saved somewhere, you dont want to lose it

Move tables - use the following as a template, script it, for all the tables in the filegroup

select top 0 *
into database.schema.table_tmp
from database.schema.table

insert into database.schema.table_tmp with (TABLOCK)
select * from database.schema.table

drop table database.schema.table

exec sp_rename 'schema.table_tmp' , 'table'

apply the indexes you scripted earlier, make sure to edit the script to build indexes on new filegroup and sort in temp db on to speed it up (my temp is on an ssd)

if required

drop all the files from the old filegroup
drop the filegroup
rename the new filegroup so it matches the name of the old filegroup

other notes

may need to move lob data, if you use the above method you shouldnt need to, if you need to the only way i could find it so right click table, select design, view properties window, change text/image file group to the new file group, click back into the table so it registers the change, then script the change.
now edit the script to enable minimal logging, if you dont you could screw up your database, get rid of the tranasaction to prevent locking

indexes

you can script indexes via gui, helpful hints - sort in tempdb, drop existing, check the filegroup. use create script option.

primary keys cannot be created with drop existing, need to drop then create on the correct filegroup

i used this script to check what objects were on the filegroup, in the where clause i restriced it to just heaps/ clustered indexes/ primary keys depending on what i was working on

--exec sp_running_SQL

SELECT
(((sum(c.reserved_page_count)*8)/1024)/1)/1024.0
FROM database.sys.indexes a
INNER JOIN database.sys.objects b ON b.object_id = a.object_id
INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
WHERE
1=1
--and b.name LIKE '%prev%'
AND d.name = 'PRIMARY'
--AND a.type_desc != 'Clustered'
--ORDER BY e.NAME,b.name
ORDER BY 1 DESC

SELECT
c.reserved_page_count,a.name,e.name+'.'+b.name,a.type_desc,*
FROM database.sys.indexes a
INNER JOIN database.sys.objects b ON b.object_id = a.object_id
INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
WHERE
1=1
--and b.name LIKE '%prev%'
AND d.name = 'PRIMARY' --and a.type_desc='HEAP'-- and is_primary_key =1
--AND a.type_desc != 'Clustered'
--ORDER BY e.NAME,b.name
ORDER BY 3-- DESC

The above script told me everything had been moved but the following script told me that wasnt true so use this to confirm, e.* should give further info if needed

select d.name,c.name,e.reserved_page_count from sys.allocation_units a
inner join sys.partitions b on a.container_id = b.partition_id

inner join sys.objects c on c.object_id = b.object_id
inner join sys.dm_db_partition_stats e on e.object_id = c.object_id and e.index_id in (0,1)
inner join sys.schemas d on d.schema_id = c.schema_id
where data_space_id =1 --and reserved_page_count >=50000
--and c.name = 'name'
order by 3 desc

i used the following to help generate scripts

SELECT

'ALTER TABLE ' +e.name+'.'+b.name+ ' ADD jlz int; CREATE CLUSTERED INDEX I ON ' +e.name+'.'+b.name + ' (jlz) with (sort_in_tempdb=on) on [DATA]; drop INDEX I ON '+e.name+'.'+b.name + '; alter table '+e.name+'.'+b.name + ' drop column jlz',
c.reserved_page_count,a.name,e.name+'.'+b.name,a.type_desc,*
FROM database.sys.indexes a
INNER JOIN database.sys.objects b ON b.object_id = a.object_id
INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
WHERE
d.name = 'PRIMARY' and a.type_desc='HEAP'
ORDER BY c.reserved_page_count DESC

--SELECT 'CREATE CLUSTERED INDEX I ON ' +e.name+'.'+b.name + ' (' + f.name + ') on [STAGING_NEW]; drop INDEX I ON '+e.name+'.'+b.name,
--c.reserved_page_count,a.name,e.name+'.'+b.name,a.type_desc,*
--FROM database.sys.indexes a
--INNER JOIN database.sys.objects b ON b.object_id = a.object_id
--INNER JOIN database.sys.schemas e ON e.schema_id = b.schema_id
--INNER JOIN database.sys.dm_db_partition_stats c ON c.index_id = a.index_id AND c.object_id = b.object_id
--Inner join database.sys.data_spaces d on a.data_space_id = d.data_space_id
--inner join database.sys.columns f on f.object_id = b.object_id and f.column_id=1
--WHERE
--d.name = 'STAGING' and a.type_desc='HEAP' and b.name like '%_bkp'
--ORDER BY c.reserved_page_count DESC

the following lets me check the log to ensure i am minimal logging, if you see 'LOP_INSERT_ROWS' then you arent minimal logging

SELECT Operation,AllocUnitName FROM fn_dblog (DEFAULT, DEFAULT) WHERE Operation = 'LOP_INSERT_ROWS'

 

Capturing Execution Plans for Overnight Processes

September 13, 2017

Scenario: I want to capture execution plans for a particular overnight process. Maybe I want to check what indexes a query is using at 3AM and compare its index usage at 7AM

Add the following code to your procedure:

DECLARE @xyz VARBINARY(MAX) = CAST(‘ANY TEXT HERE’ AS VARBINARY(MAX))
SET CONTEXT_INFO @xyz

Select @xyz and obtain the variable value

For example: 0x646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

Remove the initial 0x and make a note of the rest of the value

Create an Extended Event

add event “Query Post Execution Showplan”

add filter sqlserver.context_info equal binary data 646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

add an event file to store the output and start the Extended Event. Dont use ring buffer, I cant get the process to work with ring buffer although admitedly I havent really persevered.

Let the overnight process complete and the next day right click the event file and View Target Data, click on a record and change to Query Plan tab in the bottom half of your screen to view the query plan.

Detecting Database access via AD group membership

September 4, 2017

SET NOCOUNT on

SELECT id=IDENTITY (int, 1,1),’exec xp_logininfo ”’ +name +”’,”members”’ code

INTO #t

FROM sys.server_principals WHERE type = ‘G’

–SELECT * FROM #t

CREATE TABLE #r (account_name VARCHAR(100),type_desc VARCHAR(100),privilege VARCHAR(100),mapped_login_name VARCHAR(100),permission_path VARCHAR(100))

DECLARE @maxid INT = (SELECT MAX(id) FROM #t)

DECLARE @currentid INT = 1

WHILE @currentid<=@maxid

BEGIN

DECLARE @code VARCHAR(MAX) = (SELECT code FROM #t WHERE id = @currentid)

–PRINT @code

INSERT INTO #r

EXEC (@code)

SET @currentid = @currentid +1

END

IF OBJECT_id('database_name.dbo.permissions_table') IS NULL

SELECT *,GETDATE() date_captured

INTO ('database_name.dbo.permissions_table

FROM #r

IF OBJECT_id('('database_name.dbo.permissions_table_audit') IS NULL

SELECT TOP 0 *,GETDATE() date_captured, 'A' record_type

INTO ('database_name.dbo.permissions_table_audit

FROM #r

–detect additons

SELECT *,GETDATE() date_captured

INTO #additions

FROM

(

SELECT * FROM #r

EXCEPT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'A' FROM #additions

INSERT INTO ('database_name.dbo.permissions_table

SELECT * FROM #additions

–detect deletions

SELECT *,GETDATE() date_captured

INTO #deletions

FROM

(

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

EXCEPT

SELECT * FROM #r

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'D' FROM #deletions

DELETE FROM ('database_name.dbo.permissions_table

FROM ('database_name.dbo.permissions_table

INNER JOIN (

SELECT * FROM

(SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM #deletions

INTERSECT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM ('database_name.dbo.permissions_table) a ) a

ON a.account_name = permissions_table.account_name AND a.permission_path = permissions_table.permission_path

sample winscp script

June 22, 2017
option batch on
option confirm off
open USERNAME:PASSWORD@SERVER
option transfer binary
get *part_of_file_name* -filemask=>24h “output_path”
close
exit
* is a wildcard
output path must have the trailing \
to call:
“file_path\WinSCP.com” /script=”file_path”
no spaces apart from potentially in filepath and before /script

How to quickly shrink/remove a database file

March 10, 2017

How to quickly shrink/remove a database file

Quickly is a relative term and this could mean turning a 2 week task into a 2 day task.

Normally what you would do if you wanted to remove a datafile is clear it of data and the remove it. You would use the DBCC SHRINKFILE (LogicalFileName, EMPTYFILE) command to do this.

You might also instead of shrinking a file, delete it and then recreate it at the desired size

Depending on the size of the file this could take days, maybe even weeks. I have witnessed the empty file moving data at a rate of 1MB a minute so if your file is several TB it will take a very long time.

However there are things we can do to speed it up

First of all put your file into read only mode you can do this using DBCC SHRINKFILE (LogicalFileName, EMPTYFILE). From books online “Emptyfile assures you that no new data will be added to the file.” which is exactly what we want

We now want to move all the tables and indexes off the file and onto other files in the filegroup we do this by rebuilding all the heaps and indexes. Use the following code to generate scripts

select concat(‘exec (”alter table ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,b.index_id
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where a.type_desc = ‘Heap’ and data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
UNION ALL
select DISTINCT concat(‘exec(”alter INDEX ALL ON ‘,quotename(d.name),’.’,quotename(object_name(a.object_id)), ‘ rebuild”)’,’
GO’) a ,reserved_page_count ,1
from sys.indexes a with(NOLOCK)
inner join sys.dm_db_partition_stats b with(NOLOCK) on a.object_id = b.object_id
inner join sys.objects c with(NOLOCK) on c.object_id = a.object_id
inner join sys.schemas d with(NOLOCK) on d.schema_id = c.schema_id
where data_space_id = 1 and a.index_id = b.index_id and c.type = ‘U’
ORDER BY 2 DESC
Before running this code you will need to disable any columnstore indexes on your tables
select concat(‘ alter index ‘, a.name , ‘ on ‘,c.name,’.’,b.name , ‘ disable’) from sys.indexes a
inner join sys.objects b on a.object_id = b.object_id
inner join sys.schemas c on b.schema_id = c.schema_id
where a.type > 5

When this has completed you can check the amount of space left using the following code

IF (SELECT OBJECT_ID(‘tempdb..#a’)) IS NOT NULL DROP TABLE #a
IF (SELECT OBJECT_ID(‘tempdb..#b’)) IS NOT NULL DROP TABLE #b
IF (SELECT OBJECT_ID(‘tempdb..#c’)) IS NOT NULL DROP TABLE #c
SELECT DB_NAME(a.database_id) db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE ‘ + QUOTENAME(DB_NAME(a.database_id))
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + DB_NAME(a.database_id) + ”’,”’
+ a.name + ”” get_file_size
INTO #a
FROM sys.master_files a
INNER JOIN sys.databases b ON b.database_id = a.database_id
WHERE b.state_desc = ‘ONLINE’ AND b.name !=’tempdb’ –master files will not give accurate data for temp log;
AND b.user_access_desc = ‘MULTI_USER’
UNION ALL
SELECT ‘tempdb’ db_name ,
a.name ,
CAST(( 8.0 * size ) / 1024.0 AS DEC(10, 2)) size ,
growth ,
max_size ,
is_percent_growth ,
‘USE [tempdb]’
+ ‘;SELECT (8.0*FILEPROPERTY(”’ + a.name
+ ”’,”SpaceUsed”))/1024.0,”’ + ‘tempdb”,”’
+ a.name + ”” get_file_size
FROM tempdb.sys.database_files a
SELECT get_file_size INTO #b FROM #a

WHILE EXISTS (SELECT 1 FROM #b)
BEGIN
DECLARE @sql VARCHAR(MAX) = (SELECT TOP 1 get_file_size FROM #b ORDER BY 1)
DECLARE @a TABLE (size DEC(10,2),name_db VARCHAR(100),name_file VARCHAR(100))

INSERT INTO @a
EXEC(@Sql)

DELETE FROM #b WHERE get_file_size = @sql
END

SELECT name_db,name_file,a.size,a.size-b.size AS free_space,CAST(100*(a.size-b.size)/a.size AS DEC(10,2)) free_space_percentage,a.growth,a.is_percent_growth,GETDATE() collection_time
INTO #c
FROM #a a
INNER JOIN @a b ON a.name = b.name_file AND a.db_name = b.name_db
SELECT * FROM #c

If there is still a significant amount of space being used in the file you can check what it by using the following

select top 1 * from sys.dm_db_database_page_allocations(7,NULL,NULL,NULL,’limited’) where allocated_page_file_id = 8 and object_id > 100
Where 7 is the database id, I use top 1 because I find the code can take more than 24 to return a complete data set

you can then rebuild the heap and tables to shift the data off the file

Formatting HTML for send_dbmail proc

March 2, 2017

taken from: http://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email

Procedure

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO

Usage

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = 'bar@baz.com;',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

 

 

Shrinking a database

February 14, 2017
I used the following scripts to shrink down a database.

1 create a new filegroup + files
2. move all heaps to the new filegroup
3. move all indexes to the new filegroup
4. Get the files for the old filegroup right
5. Move the tables back and drop any clustered indexes created in (2)
6. Remove the new files and filegroup

--this will create a clustered index on heaps on the new filegroup, 167 refers to varchar 8000, which is none indexable

SELECT 'create clustered index jlc on ' + d.name + '.' + c.name+ '('+ QUOTENAME(e.name) + ') on [secondary]',* FROM sys.indexes a
INNER JOIN sys.data_spaces b ON b.data_space_id = a.data_space_id
INNER JOIN sys.objects c ON c.object_id = a.object_id
INNER JOIN sys.schemas d ON d.schema_id = c.schema_id
INNER JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY column_id) r FROM sys.columns WHERE system_type_id != 167 AND user_type_id !=167) e ON e.object_id = a.object_id AND e.r = 1
WHERE b.name = 'Primary' AND c.type = 'U' AND a.type_desc = 'HEAP'

--this will drop the above indexes

SELECT 'DROP INDEX jlc ON ' + c.name + '.' + b.name FROM sys.indexes a
INNER JOIN sys.objects b ON b.object_id = a.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
WHERE a.name = 'jlc'



--change the filegroup name in two separate places
--this will move indexes to the new filegroup 
--found on google, then modified

SELECT ' CREATE ' +
 CASE 
 WHEN I.is_unique = 1 THEN ' UNIQUE '
 ELSE ''
 END +
 I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
 QUOTENAME(I.name) + ' ON ' +
 SCHEMA_NAME(T.schema_id) + '.' + QUOTENAME(T.name) + ' ( ' +
 KeyColumns + ' ) ' +
 ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
 ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +
 CASE 
 WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
 ELSE ' PAD_INDEX = OFF '
 END + ',' +
 'FILLFACTOR = ' + CONVERT(
 CHAR(5),
 CASE 
 WHEN I.fill_factor = 0 THEN 100
 ELSE I.fill_factor
 END
 ) + ',' +
 -- default value 
 'SORT_IN_TEMPDB = OFF ' + ',' +
 CASE 
 WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
 ELSE ' IGNORE_DUP_KEY = OFF '
 END + ',' +
 CASE 
 WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
 ELSE ' STATISTICS_NORECOMPUTE = ON '
 END + ',' +
 ' ONLINE = OFF ' + ',' +
 CASE 
 WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
 ELSE ' ALLOW_ROW_LOCKS = OFF '
 END + ',' +
 CASE 
 WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
 ELSE ' ALLOW_PAGE_LOCKS = OFF '
 END + ',DROP_EXISTING = ON ) ON [' +
 'PRIMARY' + ' ] ' + CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM sys.indexes I
 JOIN sys.tables T
 ON T.object_id = I.object_id
 JOIN sys.sysindexes SI
 ON I.object_id = SI.id
 AND I.index_id = SI.indid
 JOIN (
 SELECT *
 FROM (
 SELECT IC2.object_id,
 IC2.index_id,
 STUFF(
 (
 SELECT ' , ' + QUOTENAME(C.name) + CASE 
 WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
 = 1 THEN 
 ' DESC '
 ELSE 
 ' ASC '
 END
 FROM sys.index_columns IC1
 JOIN sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 
 0
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY
 IC1.object_id,
 C.name,
 index_id
 ORDER BY
 MAX(IC1.key_ordinal) 
 FOR XML PATH('')
 ),
 1,
 2,
 ''
 ) KeyColumns
 FROM sys.index_columns IC2 
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY
 IC2.object_id,
 IC2.index_id
 ) tmp3
 )tmp4
 ON I.object_id = tmp4.object_id
 AND I.Index_id = tmp4.index_id
 JOIN sys.stats ST
 ON ST.object_id = I.object_id
 AND ST.stats_id = I.index_id
 JOIN sys.data_spaces DS
 ON I.data_space_id = DS.data_space_id
 JOIN sys.filegroups FG
 ON I.data_space_id = FG.data_space_id
 LEFT JOIN (
 SELECT *
 FROM (
 SELECT IC2.object_id,
 IC2.index_id,
 STUFF(
 (
 SELECT ' , ' + C.name
 FROM sys.index_columns IC1
 JOIN sys.columns C
 ON C.object_id = IC1.object_id
 AND C.column_id = IC1.column_id
 AND IC1.is_included_column = 
 1
 WHERE IC1.object_id = IC2.object_id
 AND IC1.index_id = IC2.index_id
 GROUP BY
 IC1.object_id,
 C.name,
 index_id 
 FOR XML PATH('')
 ),
 1,
 2,
 ''
 ) IncludedColumns
 FROM sys.index_columns IC2 
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
 GROUP BY
 IC2.object_id,
 IC2.index_id
 ) tmp1
 WHERE IncludedColumns IS NOT NULL
 ) tmp2
 ON tmp2.object_id = I.object_id
 AND tmp2.index_id = I.index_id
WHERE ds.name = 'Secondary'

Unable to start up Server Instance

February 6, 2017

So I had a little panic situation earlier today however it is now resolved and this is how I resolved it.

The Preprod instance was down and I could not get it back up again.

When I tried to start the instance I received an error message telling me that “The request failed or the service did not respond in a timely fashion”

I checked event viewer, but there nothing helpful there “The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.”

I then tried to start the service using services.msc again no good “This service on local computer started and then stopped. some service stop automatically if then are not in use by other service or programs ”

I then checked the log file “E:\Microsoft SQL Server\MSSQL11.PREPROD\MSSQL\Log\ERRORLOG” Also unhelpful “Failed allocate pages: FAIL_PAGE_ALLOCATION 1”

Finally I rememeber changing the memory setting on preprod earlier today and allocating all it’s to memory to the default instance and so I used the startup parameter -f , which starts the instance up in minimal config mode, to quote books online “This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.” This was successful I got the instance up and running in min config mode :>D

I now logged in on the DAC connection and ran EXEC sys.sp_configure. max server memory (MB) had a config value of 128 and a running value of 2147483647.

I ran the following code
EXEC sys.sp_configure N’max server memory (MB)’, N’10000′
GO
RECONFIGURE WITH OVERRIDE
GO

we now had a config value of 10000 and a run value of 10000

I stopped the server, removed the minimal config startup parameter and restarted the service. Success.

Looking through the logs there is no record from when I changed the maximum memory value earlier in the day so I assume something must have gone wrong and it took a stupidly large value in error which caused the troubles.

Anyway it is now fixed. Yay.