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? Thankyou, Best Regards, SQLHungry
Every table should have a clustered index: select * from sys.tables t where not exists (select * from sys.indexes si where t.object_id = si.object_id and type_desc = 'CLUSTERED'); Every table should have a primary key: select * from sys.tables t where not exists (select * from sys.key_constraints kc where t.object_id = kc.parent_object_id and type = 'PK'); No tables should exist on the primary filegroup (modified from [a Pinal Dave blog post](
http://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/); I haven't tested this part): SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND o.type = 'U' -- User Created Tables and f.name = 'PRIMARY'; Primary keys should be named PK_TableName: select t.name as TableName, kc.name as PrimaryKeyName from sys.tables t left outer join sys.key_constraints kc on t.object_id = kc.parent_object_id and kc.type = 'PK' where 'PK_' + t.name <> kc.name; 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.
**EDIT** 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 :) **END EDIT** 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. --Tables lacking clustered index select * from sys.tables where object_id not in (select object_id from sys.indexes where type_desc='CLUSTERED') --Tables lacking primary key select * from sys.tables where object_id not in (select parent_object_id from sys.key_constraints where type='PK') --Clustered indexes and heaps on PRIMARY. Inspiration from
http://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/ SELECT o.[name] as TableName, i.name as IndexName FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id AND f.name='PRIMARY' INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] AND o.type='U' WHERE i.data_space_id = f.data_space_id AND o.type = 'U' AND i.index_id in (0,1) --Clustered indexes and heaps GO --Find primary keys which are not named 'PK%' select * from sys.key_constraints where type='PK' and name not like 'PK%'
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 ?? Thanks, Regards, sqlhungry