TSQL Cheat Sheet

A collection of tsql information I have found/used:

Easy way to find out sql server version
SELECT @@VERSION

Add a primary key to existing table
Playing with Entity Framework, I came across a table that didn't have a real primary key. This table consisted of a Department ID and a Department Description, but both fields need to be editable. In the db, the ID field was set as the primary key, so whenever it needed to be changed, the support engineer had to completely delete the field and re-add with the correct information.

That's ok when working directly in the database, but EF doesn't like it at all. So I added a primary key by doing the following:

Find the existing constraint:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint FROM sys.objects where OBJECT_NAME(parent_object_id)='TableName' and type_desc LIKE '%CONSTRAINT'
Drop the constraint:
ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;
GO
Add the primary key:
ALTER TABLE TableName
ADD PKName BIGINT IDENTITY;
GO
This does not set the primary key. Open the designer and assign the primary key to the field just added. 




Regular Expressions
You can use regular expressions in a query!  Use it with NOT LIKE to create a double negative:

(Returns only numeric codes)
SELECT * FROM Location
WHERE LocationCode NOT LIKE '%[^0-9]%'

(Returns only alpha)
SELECT * FROM Location
WHERE LocationCode NOT LIKE '%[^a-z]%'

(Returns alpha-numeric)
SELECT * FROM Location
WHERE LocationCode NOT LIKE '%[^0-9a-z]%'