Create a Table dynamically

Is it possible to create a table dynamically ? I would like to do
something like this:
DECLARE @.TableName varchar(100)
SET @.TableName = 'toto'
CREATE TABLE @.TableName
It returns a syntax error.
Thank you.jerome.bellan...@.lycos.com wrote:
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
Why would you want to do that though? Good design practice generally
requires a schema that is static at runtime.
Creating tables dynamically might occassionally be useful during
development and you can use TSQL's EXEC statement for that. If you
attempt the same in transactional production environment however you
would pay a heavy price in terms of scalability, security and
manageability.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||try this...
declare @.tablename varchar(20)
select @.tablename = 'table'
EXEC ('CREATE TABLE ' + @.tablename + ' ( col1 varchar(20))')
Immy
<jerome.bellanger@.lycos.com> wrote in message
news:1141039414.594591.289750@.i39g2000cwa.googlegroups.com...
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.
>|||Thank you. It is perfect.|||Not like this.
Try this, instead:
DECLARE @.TableName varchar(100)
SET @.TableName = 'toto'
exec ('CREATE TABLE ' + @.TableName + ' (column1 int)' )
Cheers.
Deck
jerome.bellanger@.lycos.com wrote:
> Is it possible to create a table dynamically ? I would like to do
> something like this:
> DECLARE @.TableName varchar(100)
> SET @.TableName = 'toto'
> CREATE TABLE @.TableName
> It returns a syntax error.
> Thank you.

Create @tablename gives syntax error

I am trying to create a table named "salesfacts" as follows:
declare @.tname1 as char(30)
set @.tname1 = 'SalesFacts'
CREATE TABLE @.tname1 ([cuid] [char](15)...
and get an error : Incorrect syntax near '@.tname1' ...
The error is related to the variable name - and not to what follows it, i.e.
'([cuid ...', as I could prove.
Only "Create table salesfacts ... " gives no error.
How can I use a variable name here instead of hard-coding the table name?
TIA> How can I use a variable name here instead of hard-coding the table name?
You'll need to build the desired SQL statement as a string and then execute
it dynamically using EXECUTE or sp_executesql. See Erland's thorough
article on the subject: http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"PeterK" <invalid@.verizon.net> wrote in message
news:OZOGuLf0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>I am trying to create a table named "salesfacts" as follows:
> declare @.tname1 as char(30)
> set @.tname1 = 'SalesFacts'
> CREATE TABLE @.tname1 ([cuid] [char](15)...
> and get an error : Incorrect syntax near '@.tname1' ...
> The error is related to the variable name - and not to what follows it,
> i.e. '([cuid ...', as I could prove.
> Only "Create table salesfacts ... " gives no error.
> How can I use a variable name here instead of hard-coding the table name?
> TIA
>
>|||"PeterK" <invalid@.verizon.net> wrote in message
news:OZOGuLf0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>I am trying to create a table named "salesfacts" as follows:
> declare @.tname1 as char(30)
> set @.tname1 = 'SalesFacts'
> CREATE TABLE @.tname1 ([cuid] [char](15)...
> and get an error : Incorrect syntax near '@.tname1' ...
> The error is related to the variable name - and not to what follows it,
> i.e. '([cuid ...', as I could prove.
> Only "Create table salesfacts ... " gives no error.
> How can I use a variable name here instead of hard-coding the table name?
> TIA
>
>
You can't parameterize table names like that. But why would you want to in
this instance?
CREATE TABLE SalesFacts ([cuid] [char](15)...
David Portas
SQL Server MVP
--

Covering indexes in SQL Server 2005

Hi,
To look up information about indexes for a given table in SQL Server
2005, I can use:
sp_helpindex TableName
or
select * from sys.indexes where object_id = object_id('TableName')
But this just gives me information about the column or columns on which
the index has been defined. How do I find out what columns may have
been included in the index?
For example, if I created the following index:
CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
Where will I find complete information about IX_1 - which also
indicates whether this is a covering index and if yes - what columns
were included?
Thanks much,
SmithaHi Smitha
You'll need to join sys.indexes with sys.index_columns, and sys.columns.
Here is a view I created to do that:
CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name, 'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
So after creating this view, you can use it as follows:
SELECT * FROM get_index_columns
WHERE object_name = 'TableName'
--
HTH
Kalen Delaney, SQL Server MVP
<smithabreddy@.gmail.com> wrote in message
news:1155047736.933287.185730@.i42g2000cwa.googlegroups.com...
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>|||Hi
Check out sys.index_columns and the is_included_column bit/
John
"smithabreddy@.gmail.com" wrote:
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>