Replicating database objects through c# code

Question

Hi,

I need to create database objects (table, index, keys, views, functions, etc.) through the c#.net code. The user specifies the name of the database object to be replicated and the new name given to the copied object.

So suppose user selects existing table TableA and the new name TableB, my code should create TableB having the same structure (columns, etc.) as TableA.

I am okay with the actual creation part. I need tips on how to get the TableA metadata (structure).

Which is the best way to achieve the same?

Thanks very much :)

Answer 1

Take a look into SQL Management Objects (SMO), its a library developed by microsoft for performing management tasks on SQL server databases through managed code. It includes code to script databases and the objects within them, which should satisfy a large portion of your applications requirements

Answer 2

Have a look at a library called SMO (SQL Server Management Objects): http://msdn.microsoft.com/en-us/library/ms162169.aspx

Answer 3

You can get this information from the INFORMATION_SCHEMA schema.

For example:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA'

will give you all the columns. See here for a tutorial.