Database Schema validation against a database

Question

Database Schema validation against a database

madhusudhan_nm

Grasshopper

Points: 15
More actions
June 2, 2008 at 8:00 am

#93314

I have a live SQL Server(or any other) database of a product. The database is supposed to follow some schema. I have the schema with me (I did not yet decided in which format the schema is, probably, an XML?). Now, I want to make sure the live database is following the schema.
Actually I want to use this validation in data migration, i want to make sure the database is following the schema, before processing it for ETL.
Any help is highly appreciated, also I have not yet decided the actual representation of the schema, please suggest.
Thanks
Madhu

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not really clear on what you're asking. Are you trying to find out how to validate some data before you insert it into a database?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Grant Fritchey SSC Guru Points: 399332 More actions · Answer 2

I'm not entirely sure I understand the question, but I'll take a shot.

You can export the entire schema to a single SQL file. Then you can use a third party tool, like Red Gate SQL Compare, to compare the file to the actual database. That's one approach. We use Microsoft's Visual Studio Team Edition for Database Professionals (DBPro) and do the same types of compares between individual database objects stored in source control (TFS in our case) and live databases.

Is that at all helpful?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

madhusudhan_nm Grasshopper Points: 15 More actions · Answer 3

Thanks very much for the reply.

Actually, I have database received from the customer (having data). I want to check whether all the tables, columns, constraints, indexes etc. are correct and following the schema.

Basically i want to check is there any difference between what is there and what i am expecting in the database.

I want to do this programmatically.

thanks

Grant Fritchey SSC Guru Points: 399332 More actions · Answer 4

I still land back at using one of the third party tools. They're very effective. I know the Red Gate tool can be run from the command line so you can incorporate it into some automated process. I've done it. I'll be the other offerings from the other vendors mentioned above will work the same way.

I did a search in the scripts availabe here on SSC. There are some TSQL methods you could use for doing this. Try one of these.

Still, I'm lazy enough I'd rather use one of the other tools.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning