question

SteffSullivan avatar image
SteffSullivan asked

Retrieve meta-data for information_Schema on mssql

Does anybody have a script that retrieves the meta-data for information_Schema or knows categorically that the information schema collection in mysql and mssql are the same? I need to regularly pull meta-data from a number of databases under my control and store the results. To do so I first need to build some tables to store the information and generate the sql for selecting the data from both db types but without being able to interrogate the structure of the mssql information_schema I'd be building blind.
meta-datainformation_schema
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
They are not the same. MySQL has documented a variety of proprietary extensions to their implementation (see TABLES example link below) MySQL and SQL Server both have the definitions of their INFORMATION_SCHEMA views available online so you can find the column names you need from each. Just for a quick sample... Here is the MySQL INFORMATION_SCHEMA.TABLES view - [ http://dev.mysql.com/doc/refman/5.0/en/tables-table.html][1] Here is the SQL Server INFORMATION_SCHEMA.TABLES view - [ http://msdn.microsoft.com/en-us/library/ms186224.aspx][2] Based on their documentation, they should be alike enough to build what you are after (it looks like they both implement the standard pretty well). Both products are also available for no cost (SQL Server Express/SQL Server Evaluation) so you can fire up both and query them directly to validate your queries. No need to build blind. Here is the complete list of SQL Server INFORMATION_SCHEMA views - [ http://msdn.microsoft.com/en-us/library/ms186778.aspx][3] Here is the complete list of MySQL INFORMATION_SCHEMA views - [ http://dev.mysql.com/doc/refman/5.0/en/information-schema.html][4] [1]: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html [2]: http://msdn.microsoft.com/en-us/library/ms186224.aspx [3]: http://msdn.microsoft.com/en-us/library/ms186778.aspx [4]: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.