Which one is best INFROMATION_SCHEMA or Sys objects?
I plan to prepare generic scripts which would provide difference between two databases. If any schema difference between two tables such as new column could be added in one database table. A script should be created to find out that column and ADD the column as update script. Which one is best method? Either querying INFROMATION_SCHEMA? Or Querying Sys objects? Please do suggest.
What are the pros and cons of both of them? Please do explain.
What are the pros and cons of both of them? Please do explain.
Hi,
sysobjects is very likely to be version dependant. That is, your script may end up being a dynamic bowl of spaghetti ...
information_schema not so much.
HTH
David
sysobjects is very likely to be version dependant. That is, your script may end up being a dynamic bowl of spaghetti ...
information_schema not so much.
HTH
David
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Hi Scott,
I'd be interested in reading the issue re INFORMATION_SCHEMA in BOL if you have a link.
Regards
David
I'd be interested in reading the issue re INFORMATION_SCHEMA in BOL if you have a link.
Regards
David
Look in Books Online under "INFORMATION_SCHEMA.TABLES
ASKER
There is a conflict between the statements provided by ScottPletcher and deviprasadg. Experts please do justify which one is best to follow.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Finally I could conclude as Sys tables are better than INFORMATION_SCHEMA. Isn't it?
Yes.
>> So in my judgement then, I'd be comfortable using information_schema views for some of the more pedestrian queries. <<
Why? If someone later uses that code for schema-related info, they could get serious errors. Why write code that invites errors later? And how do you know you even have the right row in the I_S view, since you can't verify the schema it belongs to!
>> So in my judgement then, I'd be comfortable using information_schema views for some of the more pedestrian queries. <<
Why? If someone later uses that code for schema-related info, they could get serious errors. Why write code that invites errors later? And how do you know you even have the right row in the I_S view, since you can't verify the schema it belongs to!
Hi Scott,
>>>> So to be honest, I'm using the sys views for much of my work.
Regards
David
>>>> So to be honest, I'm using the sys views for much of my work.
Regards
David
ASKER
Thanks.
ASKER
Could you please suggest some links to get start with sys objects and some sample queries using them?
>> So in my judgement then, I'd be comfortable using information_schema views for some of the more pedestrian queries. <<
>> Hi Scott,
>>>> So to be honest, I'm using the sys views for much of my work.
Regards
David
<<
Why not just clearly state: never use I_S views!?
How can you use them for even "pedestrian" work, when you can't accurately identity which row in the I_S view you need to SELECT, since the schema in that row may not be correct/current?
>> Hi Scott,
>>>> So to be honest, I'm using the sys views for much of my work.
Regards
David
<<
Why not just clearly state: never use I_S views!?
How can you use them for even "pedestrian" work, when you can't accurately identity which row in the I_S view you need to SELECT, since the schema in that row may not be correct/current?
You are always much better off querying Information_Schema, because it hides the implementation details of the objects in sysobjects.
Refer:
http://stackoverflow.com/questions/4381765/information-schema-vs-sysobjects
http://beyondrelational.com/modules/1/justlearned/0/tips/11481/sql-server-use-informationschema-views-over-sysobjects-as-internal-tables-might-change-between-versi.aspx