Create Table Question T-Sql

Why can't I do this :

declare @.strUserName varchar (255)
declare @.strTableName varchar (255)

set @.strUserName = 'alan'
set @.strTableName = 'Table' + @.strUsername

CREATE TABLE @.strTableName(
[DistinctLifeID] [int] NULL,
) ON [PRIMARY]

Is there a way to pass a variable Name into a create statement for a table in T-SQL

Cheers :rolleyes:Try it with the EXECUTE statement:

DECLARE @.tablename varchar(255)
EXEC ('CREATE TABLE ' + @.tablename + '...')

peter|||Thanks Peter

You are indded the man

:cool:

create table

i have this code: this is working in mysql but not in sql server 2000. could someone help me.
CREATE TABLE Project (ProjectCode varchar(100),MapNumber varchar(100),Status float(3,2));
i've got this message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
there is no float in SQL. Try decimal instead. you can also get rid of the colon at the end.
|||thank you very much.|||

What kind of ProjectCode is a VARCHAR(100)? Ditto for MapNumber?!?
You need to use proper datatypes and domains to ensure you don't have garbage data.

|||

harrha19 wrote:

i have this code: this is working in mysql but not in sql server 2000. could someone help me.
CREATE TABLE Project (ProjectCode varchar(100),MapNumber varchar(100),Status float(3,2));
i've got this message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

There is Float data type in SQL Server what is wrong is you are setting precison which you cannot do with Float in SQL Server You can only set precision and scale in Decimal and Numeric. Float is seldom used in SQL Server but most T-SQL relational algebra and calculus functions are in Float. Hope this helps.

Create Proc Parameter Issue

Hi All,
I have created a stored proc that is set to accept @.username =
varchar(40)...it fails when the username is FULLY qualified with Domain name
...ex: 'MyDomain\Username'...how do I get my procedure to except this FULL
name?
Thanks...M.Please post some sample code on how you execute that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Michelle" <smiley2211@.yahoo.com> schrieb im Newsbeitrag
news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have created a stored proc that is set to accept @.username =
> varchar(40)...it fails when the username is FULLY qualified with Domain
> name ...ex: 'MyDomain\Username'...how do I get my procedure to except this
> FULL name?
> Thanks...M.
>|||The datatype for usernames in SQL (as used in the system tables) is sysname,
which is equivalent to nvarchar(128). Use that instead of varchar(40).
Jacco Schalkwijk
SQL Server MVP
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have created a stored proc that is set to accept @.username =
> varchar(40)...it fails when the username is FULLY qualified with Domain
> name ...ex: 'MyDomain\Username'...how do I get my procedure to except this
> FULL name?
> Thanks...M.
>|||Yes, I tried sysname as well...still errors: "Associated statement is not
prepared"
************snippet********
CREATE PROCEDURE sp_getprivs (@.username sysname = null) AS
set nocount on
declare @.dbn varchar(30)
declare test cursor for
select name from master..sysdatabases
etc....
*****************
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23$f5sirZFHA.3780@.tk2msftngp13.phx.gbl...
> The datatype for usernames in SQL (as used in the system tables) is
> sysname, which is equivalent to nvarchar(128). Use that instead of
> varchar(40).
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:eVrMaXrZFHA.3220@.TK2MSFTNGP14.phx.gbl...
>|||Sorry...I execute this as such...
sp_getprivs 'MyDomain\Username'
Thanks...M
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:ukE3YorZFHA.2496@.TK2MSFTNGP14.phx.gbl...
> Yes, I tried sysname as well...still errors: "Associated statement is not
> prepared"
> ************snippet********
> CREATE PROCEDURE sp_getprivs (@.username sysname = null) AS
> set nocount on
> declare @.dbn varchar(30)
> declare test cursor for
> select name from master..sysdatabases
> etc....
> *****************
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23$f5sirZFHA.3780@.tk2msftngp13.phx.gbl...
>|||Try using delimiters:
sp_getprivs '[MyDomain\Username]'
Jacco Schalkwijk
SQL Server MVP
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:OhhR1qrZFHA.2412@.TK2MSFTNGP10.phx.gbl...
> Sorry...I execute this as such...
> sp_getprivs 'MyDomain\Username'
> Thanks...M
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:ukE3YorZFHA.2496@.TK2MSFTNGP14.phx.gbl...
>|||Thanks, that worked...
...M
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ui3WLisZFHA.2884@.tk2msftngp13.phx.gbl...
> Try using delimiters:
> sp_getprivs '[MyDomain\Username]'
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Michelle" <smiley2211@.yahoo.com> wrote in message
> news:OhhR1qrZFHA.2412@.TK2MSFTNGP10.phx.gbl...
>

Create New Table

hi i have one question,

i have created a table, let's say CallerList:

CREATE TABLE MyDB.[dbo].[CallerList]
(
[pid] [int] NOT NULL,
[Name] [varchar] NULL,
[Surname] [varchar] NULL,
[Phone] [int] NULL,
[Date] [datetime] NOT NULL

)

now i'd like to create table CallerList1, and i want it to have same names of columns: pid, Name, Surname, Phone, Date, but i don't want to create it in the way as i wrote up, is it possible to somehow COPY these column names from CallerList and not to wirte the whole code again? Like just the names of columns and their properties to have the same...

thanx

Hi,

this should help:

SELECT * INTO CallerList1 FROM CallerList WHERE 0 = 1

So you copy only the structure to the new table CallerList1

--Andreas