get rich schema details of all or some tables

Total article views: 469 | Views in the last 30 days: 5

 Rate this |   Join the discussion |  Briefcase

to get information about tables+columns+primarykeys+foreignkeys+identity, i firstly used SQL server built-in INFORMATION_SCHEMA.xxx. however, my company have various DBAs and each of them owns objects. when using INFORMATION_SCHEMA.xxx, it retrieves data based on current login (version 2005 resolves this schema v.s. object owner problem already... yeah) , which bring me a great trouble. if you want to see problems, try to use INFORMATION_SCHEMA.xxx to get primary key details of objects not own by current login. so i develop my own version, which is not related to login. try for yourself. btw... support version 7/2000/2005


the process uses two functions together
1) infFKfrom to get foreign keys
2) infTB to compose fk-data with others

you can use the function these ways...
select * from infTB('%') --> to get all info of user tables
select * from infTB('%some%table%') --> to get some

good luck

Total article views: 469 | Views in the last 30 days: 5