Tag Archives: SQL

How to create a unique filtered index as a substitute for unique constraints in MSSQL

You can enforce unique constraints on a column in SQL using a basic ‘create constraint’ command:

…but doing so on a column that allows null values will yield an error like the one below:

In these instances, your best bet is a unique filtered index. There’s some pretty in-depth info from Redgate on the subject, but the syntax is pretty similar to creating a normal nonclustered index, just with the addition of a filter and unique clause:

And voila – success:

Share on Facebook

Variable reference for common date calculations

I spend a good portion of a typical workday writing T-SQL code. Often enough, I need to calculate a date variable based on the current date. Here’s my reference scratch for some commonly used date calculations.

Share on Facebook

How to create a script to re-create all constraints in SQL

Share on Facebook

How to disable and enable all constraints on all tables in MSSQL

I find myself making backend changes lately within databases with a ton of check constraints. To disable all of the constraints on all tables within the current SQL database, the following commands come in handy.

To disable:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To re-enable:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Share on Facebook

32 bit ODBC config on 64 bit windows

Got a legacy 32 bit app that uses DSNs, but you’re running it on Windows 7 x64 or Win2008 R2? Be sure you setup your DSN under the 32 bit ODBC utility. It’s the same interface, just the 32 bit version, but the utility in Administrative Tools is for the native 64 bit platform so your 32 bit app won’t even be able to see the DSNs you create there.

Share on Facebook

How to view MySQL Database size for all DBs

Got a database that’s sucking up disk space but don’t want to dig through the filesystem? MySQL can do most of the work for you with this query:

 

Share on Facebook

How to list the table size and rowcount for tables within a MSSQL DB

Incredibly handy query; originally from here.

 

Share on Facebook

How to give a user privileges on ALL databases in MSSQL

Share on Facebook

How to remove whitespace from the end of fields in MSSQL resultsets

MSSQL only provides rtrim() and ltrim() functions for removing whitespace from your result-sets. I lurked around on some forums to find out why there’s no trim() function and the answer was somewhat obvious I guess: it would be redundant to have that since calling both isn’t very expensive and accomplishes what you need.

To trim from the right (in the case of having data in a CHAR field that’s padded-right):

To trim from the left (in the case of having a lazy UI developer that didn’t validate form input before the insert statement):

To trim both. call both:

 

You could also create your own trim function that’s just a wrapper for both but this makes your code less portable.

Share on Facebook

How to search all fields of all tables in MSSQL

Every developer has needed this at some point in time, especially when you’re dealing with a database schema that you didn’t create yourself. I found it here and am re-posting it for the sake of convenience.

 

 


Share on Facebook