Check Constraints can help enforce the all or nothing approach when it comes
If you have a set of columns inside your table that you want to allow nulls in, however if one of the columns is updated force all columns in the set to be updated, use a check constraint. In my case, I had 3 columns for delete info, which were nullable. However, if one value was updated in there, I want all three of the delete columns to require updating. I created the script below to generate the creation and removal of these constraints on a list of tables:
SSMS 2012 Extender for the times you want some organization to the random pile
When dealing with large amounts of objects in a database, navigation can be tedious with SSMS object explorer. This extender organizes the objects into groups based on schema, helping a developer easily navigate to the appropriate object. The current version didn’t work for views, but the other objects were grouped effectively. Highly recommend!
TSQL Snippet for viewing basic info on database principals and their permissions
Quick snippet I put together for reviewing basic info on database users/principals, permissions, and members if the principal is a role.:
A moment of void in the cranium reveals a recursive computed column with an
Msg 402, Level 16, State 1, Line 67 The data types varchar and void type are incompatible in the add operator.
I came across this error today when I accidentally used a computed column in a temp table, that referenced itself. This very unhelpful message was caused by referring to the computed column itself in the computed column definition, ie typo. Beware!
View computed columns in database
Snippet to quickly view computed column information. You can also view this by doing a “create table” script. This however, was a little cleaner to read and view for me.
On how to Googlify your SQL statements for future searching
For sake of future generations, let’s begin to reduce typing and reuse code we’ve built. I think we can all agree that TSQL statements are often repeated. Ideally, snippets should be created to reduce repeated typing and let us focus on logic and content. However, some statements may not really be “snippet worthy”, and just be quick adhoc queries. In the past, the solution for saving queries for reuse or reference in the future would be to just save in the projects folder …
Get synonym definitions for all databases in server
If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use
exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested.
Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp …
Calculating the next beginning of month and the current end of month
Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to find new ways to do things that take less coding, and hate date conversions that require a lot of manipulation of characters and concatenation. This was what I came up with for avoiding character conversions and concatenation for finding the current BOM (beginning of month) and EOM (end of month) values. Adjust according to your needs. Cheers! …
Installing SSMS 2012 all by it's lonesome
SQL Server Management Studio (SSMS) is not offered as a standalone download on MSDN. Installation requires the user to download the sql server installation package and choose to install only this single feature. For developers, SQL Developer edition is a great choice.
Here’s some screenshots to give you a guide on installing SSMS by itself when working with the full installer.
snippet designate a certain time of the day in getdate()
Snippet to designate a certain time of the day to evaluate in the current day. If you need to limit a result to the current date after a particular time, strip the time out of the date, and concatenate the current time together with it, and then convert back to datetime2.
Native SSMS a second class citizen no longer...
Intellisense can be a boon to adding quick development. Quick hints on scope specific variables, syntax suggestions, function descriptions and more provide a valuable tool to productive coding.Coding in SQL Server Management Studio (SSMS) has greatly improved over the version releases, but it still lags behind the power of Visual Studio’s intellisense, template insertions with “fill in the blank” functionality .
Additionally, the lack of automatic indentation means that lining …
dynamic sql and a char crash
Dynamic SQL can be helpful, but a pain to debug. I spent hours today working on figuring out why my simple date comparison in dynamic SQL wasn’t working. Found out that the remote database I was connecting to had a char date instead of a datetime. I found the comparison of CHARDATE > VARCHARDATE failed to error out, but also failed to give a proper result set. Changing the look-up to ensure both dates were converted to date fixed the issue. During this debugging I was reviewing my …