I'm trying to create a program which will generate a SQL database schema including tables, view, keys, indexes, triggers, etc... like:
CREATE TABLE TableName(....) ....
CREATE VIEW ViewName(...) ....
I know this is possible because SQL Server Management Studio does it (generate script command). However, how does it do it?
UPDATE: I forgot to mention about permissions: I'm an owner of database (in most cases) but I'm not sys-admin. Would there be any difference?
If you are targeting SQL Server only, SMO is very powerful. This is the library that SQL Server Management Studio uses, and contains classes to convert database objects into scripts.
The scripting example here is a great place to start.
for list of tables:
foreach (DataRow row in schemaTbl.Rows)
{
listBox.Items.Add(row["TABLE_NAME"]);
}
for columns from perticular table
object[] objArrRestrict;
objArrRestrict = new object[] {null, null, "Customers", null};
DataTable schemaCols;
schemaCols = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objArrRestrict);
//List the schema info for the selected table
foreach (DataRow row in schemaCols.Rows)
{
listBox.Items.Add(row["COLUMN_NAME"]);
}
Queries of database structure vary by sql server version.
The 2008 R2 page is here, which links to a TABLES page, which links to a sys.tables page, which links to a sys.objects page.
The sys.objects page has relevant samples. This only gets you the table. There are other system objects for column, triggers, views, ...
If you truly mean generate you might want to look into Entity Framework / Code first approach that will essentially boostrap your db for you (Tables, etc).
