Post reply

Retrieve meta-data for information_schema

  • July 9, 2011 at 11:07 am

    #240612

    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.

    For once mysql wins as this returns results:

    Select * from information_schema.columns where table_Schema='information_Schema'

  • July 9, 2011 at 12:40 pm

    #1350749

    The information_schema is ISO-standard. Hence, if MySQL correctly implements the ISO standard the schemas will be identical.

    However if you do what to check, this shows the definitions of all the objects in that schema.

    SELECT * FROM sys.system_objects AS o INNER JOIN sys.system_columns AS c ON o.object_id = c.object_id WHERE schema_id = SCHEMA_ID('INFORMATION_SCHEMA')

    You can join to sys.types if you want the type names for the columns as well.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2011 at 4:21 pm

    #1350770

    Thank you for the help. That's provided me with the column and table list for informationS_chema.

    Thanks 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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