Creating table at run time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating table at run time

Author

Topic

dheeraj.it
Starting Member

1 Post

Posted - 2008-08-24 : 05:44:40

hi ,
I want to create a stored procedure for creating table at run time which name is coming as parameter at run time...

Please help

waiting for reply

hi Friends

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-08-24 : 07:03:15

Check dynamic sql

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-24 : 07:11:23

Why would you want to do so ?

http://www.sommarskog.se/dynamic_sql.html
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-24 : 10:21:32

Hi dheeraj.it try this.

@TableName varchar(100)

AS

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName )

CREATE TABLE @TableName(

IndexID INTEGER IDENTITY(1,1), PID INTEGER

CONSTRAINT [IndexID] PRIMARY KEY CLUSTERED ([IndexID] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON))ON [PRIMARY]

RETURN

The extra code lets you create and index and make it a primary key and also sets it to auto increment

Hope this helps

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 04:34:11

You really don't want to do this. It might seem like a good idea at the time but creating permanent tables dynamically is (probably always) a very bad idea.

1st of all) What do you need the table to do?

If you illustrate what you are trying to achieve I'm sure you'll get a lot of suggestions about how to proceed in a better fashion.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-25 : 04:41:05

quote:
Originally posted by mobile@digitaltrendz.co.z
@TableName varchar(100)

AS

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName )

CREATE TABLE @TableName(

IndexID INTEGER IDENTITY(1,1), PID INTEGER

CONSTRAINT [IndexID] PRIMARY KEY CLUSTERED ([IndexID] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON))ON [PRIMARY]

RETURN

Hi mobile@digitaltrendz.co.z

TSQL won't let you do this using that syntax

You would have to build a string like this.


DECLARE @tableName VARCHAR(100)
DECLARE @sql VARCHAR(8000)

SET @tableName = 'myCrazyDynamicTable'

SET @sql = '

CREATE TABLE ' + @tableName + ' (
	[IndexID] INT IDENTITY(1,1)
	, [colA] VARCHAR(50)
	, [colB] VARCHAR(50)
	)'

-- Show the dynamic sql
PRINT @Sql

-- Execute it
EXEC (@sql)

This would work but it would most probably be a *stupid* idea.

dheeraj.it -- Can you tell us what you are hoping to achieve. There *will* be a better way than this.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources