Here’s a script that reports on your disabled Foreign Key and Check Constraints. It also gives you the command to re-enable them and also a rollback in case you need to disable them again and also the DBCC CHECKCONSTRAINTS command for each in case of any violations you need to Continue Reading
T-SQL
Batch Updates
I have a table with 1 Billion rows. I need to update at least 10 Million of these rows and not affect concurrency or general database performance. I cannot run an update on all the rows at once as this will lock millions of rows, use up all server memory Continue Reading
When Were My Tables Last Updated?
This short query lets you know when your user tables were last updated. This can be handy for dropping old tables and data not required any more. It’s important to remember that the data comes from DMVs and so are reset every time SQL Server is restarted so if you Continue Reading
Generate Random String in SQL
There a better ways of generating random strings in your application using PHP, C#, Python or whatever OOP language you are using but if you need to do this at the database level then here’s a clever little trick using a view and an SQL function. Basically you create a Continue Reading
Bulk Insert Using OPENROWSET()
I needed to bulk import a text file to a table but I couldn’t use BULK INSERT as the text file was only for a few columns that were present in the destination table. Rather than bulk insert to a new temporary table and insert the data from here, I Continue Reading
Database Table Sizes
This is a good query for quickly finding out general information about your table sizes in your specified database: USE [DBName] SELECT t.NAME AS TableName, s.Name AS SchemaName, p.ROWS AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.TABLES Continue Reading
SQLCMD and SQL Variables
I recently worked on a little project that involved outputting some workflow user stats from a database every hour. The scripts must produce an output file that will be transferred via SFTP to a separate database in another domain. The main bulk of the process was contained within a stored Continue Reading
List Foreign Keys, Parent and Referenced Columns
SELECT fk.name AS ForeignKey_Name, ob.name AS ParentTableName, col.name AS ParentColName, ob2.name AS ReferencedTableName, RCol.name AS ReferenceColName FROM sys.foreign_keys AS fk INNER JOIN sys.objects AS ob ON fk.parent_object_id = ob.object_id CROSS JOIN sys.objects AS ob2 INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id INNER JOIN sys.COLUMNS AS COL ON col.object_id Continue Reading
Execution Plans
I’ve been delving deep into the world of execution plans recently and I’ve come up with the following two queries which are quite handy for viewing your current execution plans stored in your plan cache. I’ve sporadically used it to look through and familiarise myself with execution plans in general Continue Reading