How to validate existing data in a table

Question

Hello Team,

I have some application databases where i need to perform data validation. I am not sure what type of data is already stored in the tables. Does there are any set of pre-defined rules / constraints with which i am cross verify data ?

Or i have to reply on the application team to provide me set of data rules which is compatible with application  

Answer 1

An extremely vague question, but in short: to validate or something with data in a database, you need to have an understanding of the business the database is to serve. Not a complete knowledge, but you at least need to have an idea. And you need to have access to people who can fill in.

And, of course, you need to have an understanding what you are supposed to "validate". I suppose that someone has given you the task who can clarify.

I've been in the situation a few times where I have had reason to look into an unknown database. Not exactly for validation, but rather to extract data. One thing which is extremely helpful is when the tables and columns have meaningful names. If names are like KONWIZBT, you will need to ask for documentation. Existing FK constraints are also very helpful to understand how tables are connected.

Answer 2

Hi @Chetan Jain

I am not sure what type of data is already stored in the tables.

To get data type for all columns in table, try this:

SELECT c.name,
       c.max_length,
       c.precision,
       c.scale,
       c.is_nullable,
       t.name
  FROM sys.columns c
  JOIN sys.types   t
    ON c.user_type_id = t.user_type_id
 WHERE c.object_id    = Object_id('table_name')

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.