sql server DMVs to identify test conditions ??

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.

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

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