Creating DB Tables at runtime(Dynamic SQL)

[devendrab]

How can I create data base objects eg tables/views/indexes at runtime thru Dynamic SQL as I know that it is possible to create in Oracle but i m not aware of how to do it in SQL Server thru ASP .


Thanx

[Johnny101]

Depends on where you're pulling your table structure/index columns or whatever, from? Here's an example of building a sql string that builds a table:

DECLARE @sql varchar(500)

set @sql = 'CREATE TABLE tblMyTable ('
set @sql = @sql + 'IDField IDENTITY int NOT null, '
set @sql = @sql + 'FirstName varchar(50) null, '
set @sql = @sql + 'LastName varchar(50) NOT null )'

EXEC(@sql)




you can place variables where ever you need them.

hope this helps,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[devendrab]

hi,

thanx for ur prompt response can i use the same script for creating stored procedures and trigers too .


Is there any binding issue or any performance degradation of database if i use this script to create database objects dynamically.

Thanx

[Johnny101]

yep, sure can.

have fun.

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org