Category: TSQL

Newest DDL edits on SQL Server

After coming back to work after the holidays, I needed a reminder of where I left off before taking 2 weeks off. SQL Server makes it easy to recall what I last worked on. Below is how to determine the last time a user table was altered or when it was created. SELECT                ,        ,...

SQL Server: How to retrieve the check constraints on columns in a specific table

sys.check_constraints contains a row for each CHECK constraint 1. parent_column_id – column id for which CHECK constraint is defined 2. parent_object_id – id of the object for whose column the constraint is defined. 3. create/modify_date – creation/modification date 4. definition – constraint’s criteria Since the name of the column the constraint is built on is not present in sys.check_constraints, you...

Setting SQL Server ROWGUIDCOL property on [uniqueidentifier] (GUID) columns

I’ve been converted – I now use GUID (UNIQUEIDENTIFIERS) instead of INT for most database tables’ primary key.  If there is only going to be 100 or less rows, I don’t bother but most everything else I feel justifies the extra bytes of GUID. It ensures the key is unique which greatly simplifies merging data in the future. A...

Convert values stored in a local server time column (default GETDATE()) to UTC time

I am upgrading a legacy application that used GETDATE() on columns (describing the row created date) in many tables. We rename the column (our column names indicate local or UTC) Set the default to now be GETUTCDATE() Convert the old values. It then becomes quite trivial to update the legacy values (step 3) to what they would have been if this had been...

Remove characters from a column

I needed to scrub a column of power meters. I needed to remove certain characters ( , hypens, / and . ) that had found their way into the system. This was fast enough on a under a hundred thousand rows. You might want to try using CLR with RegEx if you have billions to clean.