I’ve figured out a couple of tidbits today that I wanted to share. First and foremost, I’ve discovered a (new to me) option in SSMS to convert tabs to spaces. I had previously seen the “Untabify Selected Lines” option (under Edit –> Advanced), but this only seemed to remove the tabs at the beginning of the line; it would not remove in-line tabs. I’ve now found another option that will remove the tabs throughout the selected code.
Here’s how you can do it:
Disclaimer: I’m using SSMS 2008 and have not confirmed this in other versions of SSMS.
From your toolbar, right-click in the empty gray space, or click on the drop-down arrow. Navigate to Customize:
Click on the Commands tab, then navigate down to Edit –> Convert Tabs to Spaces.
Note: if you’re one of those weirdos who like tabs instead of spaces, you can convert in the other (wrong) direction, too. 😉
Once you have found the option you want, click on it and drag it to your toolbar.
Cool, huh?
Another little thing I ran into today was a mismatch between nullable and non-nullable columns in related tables. I started to perform a manual audit before I said, “There has to be a faster way.” And you know what? There was. Here’s the little script I used:
SELECT c1.name AS [columnName] , c1.is_nullable AS [FactInternetSales_isNullable] , c2.is_nullable AS [FactResellerSales_isNullable] /* replace AdventureWorksDW2008R2 with your database */ FROM AdventureWorksDW2008R2.sys.columns AS c1 JOIN AdventureWorksDW2008R2.sys.columns AS c2 ON c1.name = c2.name WHERE c1.object_id = object_id('dbo.FactInternetSales') -- replace with your table AND c2.object_id = object_id('dbo.FactResellerSales') -- replace with your table AND c1.is_nullable <> c2.is_nullable; -- find discrepancies |
This returned the following results:
columnName FactInternetSales_isNullable FactResellerSales_isNullable ---------------------- ---------------------------- ---------------------------- RevisionNumber 0 1 OrderQuantity 0 1 UnitPrice 0 1 ExtendedAmount 0 1 UnitPriceDiscountPct 0 1 DiscountAmount 0 1 ProductStandardCost 0 1 TotalProductCost 0 1 SalesAmount 0 1 TaxAmt 0 1 Freight 0 1 (11 row(s) affected) |
This script isn’t as polished as my usual, but it’s really just a quick-and-dirty way of checking for mismatches in NULL properties.
Okay, that’s all I have for now, my SQL friends. Oh, and if you know any other cool little tidbits, please feel free to share with me. 🙂
