question

sqlhungry avatar image
sqlhungry asked

sql server DMVs to identify test conditions ??

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
sqldmvunit-testing
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. I took way too long to write my answer, and missed that you had written one that actually correctly answers all of the questions. My last answer is - which I noticed when reading your answer - not fulfilling the requirement that naming should be PK_TableName, I have only PKsomething as requirement.
0 Likes 0 ·
You both got a +1 from me. Great answers from both of you.
0 Likes 0 ·
Thanks Kevin, I was wondering if we have SQL Server DMVs to do the same task. Btw, I tried you solution and they are helpful. Thanks :) Regards, Sqlhungry
0 Likes 0 ·
I'm not aware of DMVs or DMFs which do specifically what you're asking. DMVs and DMFs are more for tuning, figuring out what's going on behind the scenes, and things like extended events. Instead, the system "tables" and the INFORMATION_SCHEMA views are exposed for this purpose.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
**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%'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlhungry avatar image
sqlhungry answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@sqlhungry, you should really ask this as a separate question so any answers you receive are tied to it. Also, if any of the answers given to your original question were what you needed, please mark it/them correct so others can see which answers were most helpful.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
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. [ http://msdn.microsoft.com/en-us/library/bb510667.aspx][1] Find tables without clustered indexes: [ http://www.mssqltips.com/tip.asp?tip=2158][2] [1]: http://msdn.microsoft.com/en-us/library/bb510667.aspx [2]: http://www.mssqltips.com/tip.asp?tip=2158
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I wish I could use that. I was going over the link you posted. Seems cool. But my manager prefers the database unit testing in Visual Studio 2010 Ultimate. I have to check in all my work in TFS and not all my team members be familiar with the concept you are talking about. I would appreciate help on the technique I am talking about. Thanks :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.