August 2014 – Database Consultant

The AutoShrink code I use

--autoshrink.sql

-- list databases where autoshrink is ON
select *
from sys.databases
where is_auto_shrink_on != 0

-- make script to turn autoshrink OFF
SELECT 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF WITH NO_WAIT'
FROM sys.databases
WHERE database_id > 4
and is_auto_shrink_on != 0

Startup Parameters Greyed-out

A “Gotcha” with SQL 2012 is that the Startup Parameters are ALWAYS greyed-out if you look for them in the usual place (IE: Configuration Manager / r-click sqlserver service / properties / advanced).

Try here … Configuration Manager / r-click sqlserver service / properties / startup parameters

dow!

To Empty a Database

To stop the import of ‘already there’ data (twice) I needed to empty every table. Its a very, very good idea to back up your databases before using this code. It empties a whole database in one go!

-- empty database
use SomeDatabaseName -- ** change to subject database first & *** CHECK SERVER NAME **
go
EXEC sp_MSForEachTable 'TRUNCATE TABLE [?]'

Untrusted Keys

Here’s my mod to Aaron Lowe’s @Vendoran script to get rid of untrusted foreign keys and constraints …

--fix_untrusted_keys.sql

SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0

To download payslips from Giant as PDF’s

– View a payslip
– right-click on payslip and select Print
– in the print dialog box change Destination to ‘save to PDF’
– click Save.