Possible to Create View with passed SELECT sql statement using .NET?

Question

I'm trying to create a process in .NET to pick a .sql file from a server folder, extract the structure of it (columns, tables, etc) and save the information to a db.  My first thought was to create a View in sql based on the .sql file and then get the info using sys.dm_sql_referenced_entities on the View, but I haven't been able to find a way to create or alter a View where I can pass in the Select statement the View is based on. Is there a better path to accomplishing this? Would very much appreciate any help.

---

-Tim Lyerla

Answer 1

Hi Tim,

 

Are you using SMO, or the .NET SqlParser classes to do this or string parsing, or ..? Is this going to run as a SQLCLR stored procedure or function, or do you plan to invoke it some other way. If you’re using SQLCLR, it can’t reference SMO libraries directly.

 

And I’m unclear about what the .sql file would contain (i.e. what the format would be). Is this a .sql file from the VS2010 database project, or something else entirely?

 

To create a view using a SQLCLR stored procedure, you’d need to pass an entire “CREATE VIEW” statement in as a SqlCommand using the internal SqlConnection, there’s no other way I know to do this without using the DDL directly.

 

Cheers,

Bob

 

"TimSTL" wrote in message news:5902aa88-0872-4ad2-a8bb-c0bab39420ec...

I'm trying to create a process in .NET to pick a .sql file from a server folder, extract the structure of it (columns, tables, etc) and save the information to a db.  My first thought was to create a View in sql based on the .sql file and then get the info using sys.dm_sql_referenced_entities on the View, but I haven't been able to find a way to create or alter a View where I can pass in the Select statement the View is based on. Is there a better path to accomplishing this? Would very much appreciate any help.

---

-Tim Lyerla

Answer 2

Hi Bob,

Still evaluating the situation and looking at the pros/cons of SMO and .NET SqlParser. Trying to avoid a 3rd party parsing product. The .sql files are all SELECT statements created for various Crystal reports.  We're trying to document what tables/columns/aliases are being used for them. I've done some work with SQL and .NET, but don't have a ton of experience.

thanks,

-Tim

---

-Tim Lyerla

Answer 3

The way you’re approaching it (build view DDL and use the dependency views) is one way, another might be to run each one and analyze output from a trace. SQL Server doesn’t have anything built-in to do what you want, but you might see if there are third-party products that do that.

 

Cheers,

Bob

Answer 4

Thanks for your help Bob.

---

-Tim Lyerla