How to extract all fields from create sql statment using .Net?

Question

I am creating a windows application using VB.Net and this application will take a SQl create .sql file as a parameter and will return all fields and their data types inside in a list or array.

Example:

USE [MyDB] GO /****** Object: Table [dbo].[User] Script Date: 07/07/2009 10:16:48 ******/ 

SET 
ANSI_NULLS ON GO 

SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[User]( [UserId] [int] IDENTITY(1,1) NOT NULL, 

[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [MiddleName] 

[varchar](50) COLLATE SQL_Latin1_General_CP1_CI_A

Should Return:

UserId int, FirstName string, MiddleName string

I want to do this by any way, pure vb.net code or using RegEx.

Anyone knows the fastest way to finish this?

Answer 1

A mix of code and regular expressions. One regex to split the statements, looping through the results with code. For each statement, use a regex to determine if it's the sort of statement you care about. If it is, use another regex to pull the desired information.

Sometimes you need to do a multi-level regex approach to make things simpler -- grab the area you care about, then use another regex to pull pieces out of the match.

Answer 2

One option is to create an in-memory database using SMO, execute the script, and then iterate through the table columns.

Here is an article that does the opposite but you should be able to start down the path with it.

Disclosure: I think using splits and regex is probably a better solution but I figured you would get many of this so I thought I would throw in an alternate. After all, that is what this site is about

Answer 3

First, what flavor of SQL are you using? Second, the syntax for CREATE TABLE can be fairly "detailed". Are you dealing with a smaller subset of the general syntax that might make approaching this problem simpler?

But rather than trying to parse the statement, the fastest way might be having a trash database that you can execute the CREATE TABLE statement on, and then extracting the column names and types via

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='MyCreatedTable'

(I am assuming SQL Server here.)

Thusly,

string createTableCommandText; // "CREATE TABLE MyCreatedTable..."
using(var connection = new SqlConnection(connectionString)) {
    connection.Open();
    var createCommand = connection.CreateCommand();
    createCommand.CommandText = createTableCommandText;
    createCommand.ExecuteNonQuery();

    var schemaCommand = connection.CreateCommand();
    schemaCommand.CommandText = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyCreatedTable'";
    var reader = schemaCommand.ExecuteReader();
    while(reader.Read()) {
        Console.WriteLine(String.Format("{0}|{1}", reader["COLUMN_NAME"], reader["DATA_TYPE"]));
    }

    var deleteCommand = connection.CreateCommand();
    deleteCommand.CommandText = "DROP TABLE MyCreatedTable";
    deleteCommand.ExecuteNonQuery();
}

Sorry that it's in C#. I am not fluent enough with VB to write approximately correct code without a compiler at my fingertips.