I'm currently working on a project which is responsible to generate database model based on its schema, I want to know is there any library in .Net which is able to parsing SQL Script file and allows me to:
Get Database name from script
Get Tables name
Get each table's columns
and anything you know for generating model
Any advice will be helpful.
There isn't any library for this work except SMO. it is better that you have a query on sql system schemas. please see the below script as an example:
SELECT
SPECIFIC_NAME as SpName,
PARAMETER_NAME as ParameterName,
DATA_TYPE as DbType,
CHARACTER_MAXIMUM_LENGTH as Size
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE PARAMETER_MODE = 'IN' OR PARAMETER_MODE = 'INOUT'
This script get the stored procedure parameters. or
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName')
for getting table columns. I recommend to you that don't use SMO.
