x

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
more ▼

asked Mar 02, 2011 at 01:13 PM in Default

sqlhungry gravatar image

sqlhungry
76 10 10 12

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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; 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.
more ▼

answered Mar 02, 2011 at 01:28 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

+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.
Mar 02, 2011 at 01:55 PM Magnus Ahlkvist
You both got a +1 from me. Great answers from both of you.
Mar 02, 2011 at 01:58 PM Tim

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
Mar 02, 2011 at 02:01 PM sqlhungry
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.
Mar 02, 2011 at 02:31 PM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left

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%'
more ▼

answered Mar 02, 2011 at 01:51 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left

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

Find tables without clustered indexes: [http://www.mssqltips.com/tip.asp?tip=2158][2]

[2]: http://www.mssqltips.com/tip.asp?tip=2158
more ▼

answered Mar 04, 2011 at 03:17 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

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 :)
Mar 04, 2011 at 04:03 PM sqlhungry
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 04, 2011 at 03:12 PM

sqlhungry gravatar image

sqlhungry
76 10 10 12

@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.
Mar 04, 2011 at 06:21 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x729
x22
x7

asked: Mar 02, 2011 at 01:13 PM

Seen: 1536 times

Last Updated: Mar 02, 2011 at 01:13 PM