x

unit testing (continued...)

This is continuation from my previous question which is here

What I'm trying to do now is create a OrderedTest, and I created one more new test called the 'TestSetup', which contains the following codes:

IF OBJECT_ID('tempdb..##Database') IS NOT NULL
 DROP TABLE ##Database

CREATE TABLE ##Database(
 Id INT IDENTITY(1,1),
 DatabaseName SYSNAME PRIMARY KEY NOT NULL 
)

CREATE TABLE ##TestResult(
 DatabaseName NVARCHAR(50),
 SchemaName NVARCHAR(50),
 TableName NVARCHAR(50),
 Testname NVARCHAR(50),
 [Date] DATETIME 
)


DECLARE @@CountDatabases INT

SELECT 
 @@CountDatabases = COUNT(name) 
FROM 
 sys.databases 
WHERE
 name like 'db_%' --name of the incomming databases starts with db_ prefix

--Inserting the name of the incomming databases in ##Database
INSERT INTO ##Database SELECT name FROM sys.databases WHERE name like 'db_%'

-------------------------------------------------------------------------- Now I got all the databases to be tested in ##Databases along with its count from 'TestSetup' test. And on the Orderedlist test, this 'TestSetup' test is on top. My goal is to track the databasename, schema name, table name, testname and datetime when any of the 4 tests fail and store them into ##TestResult(which I declared in my 'TestSetup' test). For your reference, names of the 4 tests are as follows:

 1. EveryPrimaryKeyFollowsNamingStandards
 2. EveryTableHasAClusteredIndex
 3. EveryTableHasAPrimaryKey
 4. NoTableExistsOnThePrimaryFilegroup

Could you suggest me the logic and codes to implement in each test(you can give me an example in any one of the test), so that I can fill the 'TestResult' table with the informations from the failed tests. Any suggestion for the better scripts in the 'TestSetup' is also welcome, as I am not sure if ##TestResult is a good idea.(Should I just create a normal TestResult database instead??)

Thank you

Best Regards,

sqlhungry.
more ▼

asked Mar 07, 2011 at 05:46 PM in Default

sqlhungry gravatar image

sqlhungry
76 10 10 12

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

0 answers: sort voted first
Be the first one to answer this question
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:

x17
x7

asked: Mar 07, 2011 at 05:46 PM

Seen: 770 times

Last Updated: Mar 07, 2011 at 05:49 PM