Please help me with the sql server dmvs to identify the following test conditions
1) Every table should have a clustered index
2) Every table should have a primary key
3) No tables should exist on the primary filegroup
4) Primary Keys should follow the naming standard, eg “pk_”
Also, how do I implement those DMVs in my unit tests in Visual Studio 2010 Ultimate?
asked Mar 02, 2011 at 01:13 PM in Default
Every table should have a clustered index:
Every table should have a primary key:
No tables should exist on the primary filegroup (modified from a Pinal Dave blog post; I haven't tested this part):
Primary keys should be named PK_TableName:
How to use these in Visual Studio 2010 DB tests is pretty simple: use each of the above queries in a test and assert that the row count is equal to 0.
answered Mar 02, 2011 at 01:28 PM
My answer is not answering the fourth question. To do that, I should have joined sys.tables to find the tablename and check whether the PK is named PK_TableName rather than just PK% as I put it. Kevins answer is correct. Funny thing that me and Kevin related to the same blog article :)
Here's a script that I think will give you the answer to the four questions. I've never used VS2010, so I can't answer that question. I'm not even sure I understand that question. You didn't say which version of SQL Server you're using. I've tested this script on SQL Server 2008 R2.
I think you should look at policy based management to prevent all this types of errors before they even occur so you don't need to work so hard to enforce your policies manually.
Find tables without clustered indexes: http://www.mssqltips.com/tip.asp?tip=2158
I got the queries for all those 4 tasks. Thanks to you guys :).
Now I need a good suggestion for the best way to build a "unit testing project template" in Visual Studio 2010 Ultimate for these queries. I will be getting new databases every now and then into my Sql Server 2008 Management Studio from TFS. So I want to build a unit test project template which can be used for unit testing each new database i get.
Also, would it be a good idea to implement all those 4 queries into a single unit testing project template or should I create new template for each query ??
answered Mar 04, 2011 at 03:12 PM