Creating tables in SQL Server via query analyzer with relationships

I've been searching around for some info on how to set this up, but with no luck.
I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.
I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.
For instance, I can create the tables just fine....
CREATE TABLE students ( sId int NOT NULL PRIMARY KEY,
studentId varchar(50) NOT NULL,
course varchar(50)
)

CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY,
course varchar(50) NOT NULL,
sco varchar(50)
)
But, I need to set up relationships in there somehow.
Once student may have many courses (one to many) and one course may have many sco's (one to many)
SCO would be another table.
Can someone point me to a good link that would show how to complete these procedures?
Thanks all,
Zath

"sId"? "StudentId"?? "cId"?? Great start for a disaster of a system right there. If that's just a taste of your schema, you need to scrap it and start over. If you don't understand what's so wrong with it, spend a few hours reading about the relational model.
Anyway, you need to look in the BOL on DDL statements. Foreign keys are implemented via constraints:
CREATE TABLE Order_Details
(
Order_Num CHAR(12) NOT NULL
CONSTRAINT FK_Order_Detail_Orders
FOREIGN KEY (Order_Num)
REFERENCES Orders (Order_Num),
Prod_Num CHAR(8) NOT NULL
CONSTRAINT FK_Order_Detail_Products
FOREIGN KEY (Prod_Num)
REFERENCES Products (Prod_Num),
CONSTRAINT FK_Order_Details
PRIMARY KEY CLUSTERED (Order_Num, Prod_Num)
)

|||An easy way to do this, is set up your tables via enterprise manager, and then use All Tasks > Generate SQL Script. Inside the script generator, you can click on the Options tab, and click script primary keys, foreign keys, defaults and check contraints.
Nick|||Check out this T-SQL reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp

There are several ways to do it, but one of these should work

B. Use FOREIGN KEY constraints

A FOREIGN KEY constraint is used to reference another table. Foreign keys can be single-column keys or multicolumn keys. This example shows a single-column FOREIGN KEY constraint on the employee table that references the jobs table. Only the REFERENCES clause is required for a single-column FOREIGN KEY constraint.

job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)

You can also explicitly use the FOREIGN KEY clause and restate the column attribute. Note that the column name does not have to be the same in both tables.

FOREIGN KEY (job_id) REFERENCES jobs(job_id)

Multicolumn key constraints are created as table constraints. In the pubs database, the sales table includes a multicolumn PRIMARY KEY. This example shows how to reference this key from another table; an explicit constraint name is optional.

CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
REFERENCES sales (stor_id, ord_num, title_id)|||Thanks everyone for their input. I have already gotten rid of the sID and so forth or rather didn't make them primary keys, just an autonumber.
It's been a while since I developed a database, I like to stick to code and need a database refresher it seems.
Once I redo this, if I have any other problems, I repost.
Thanks,
Zath

Creating Scripts via Query Analyzer or Enterprise Manager

I'm trying to decide what is the best practice in terms of creating scripts
that, for instance, update an existing database. It seems there are a
couple of ways to do this (although I am sure there are more). It is often
suggested that you code your changes, by hand, using Enterprise Manager. As
I am looking at examples how to do this, I understand some of the basics of
doing this. For instance, if I have a tabled called Numbers and there is a
field named Employee (data type int, length 4, non nullable) that I want to
add a unique constraint to, it seems that most examples have the following
simplistic syntax:
ALTER TABLE Numbers
ADD CONSTRAINT EmployeeUnique
UNIQUE(Employee)
If I make the same change via Enterprise Manager and then click the Save
Change Script button, the amount of code it creates is much more:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Numbers ADD CONSTRAINT
IX_Numbers UNIQUE NONCLUSTERED
(
Employee
) ON [PRIMARY]
GO
COMMIT
So, is all of this extra code necessary? Are the examples I am looking at
too simplistic? Is it OK to use EM to make changes and then generate the
scripts?
Thanks.It si absolutely Ok to that in QA (the way you did).
All these SET statements, generated by EM, are actually happening whenever
you create a new session in QA, BUT behind the scene. You may see this if
you trace tsql exec from QA by Profiler.
With transactions is the same situation: in case of implicit transactions,
new T-SQL statement starts a new transaction. This explains Begin-commit
blocks inside EM-generated code.
All these settings are server defaults and can be changed.
Regards,
Marko Simic
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating script
s
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is ofte
n
> suggested that you code your changes, by hand, using Enterprise Manager.
As
> I am looking at examples how to do this, I understand some of the basics o
f
> doing this. For instance, if I have a tabled called Numbers and there is
a
> field named Employee (data type int, length 4, non nullable) that I want t
o
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>|||Do you save all your change scripts so you have a complete audit trail of al
l
changes made to your database? How you make the changes is just one part of
database change management. Check out this article on the subject
http://www.innovartis.co.uk/pdf/ In...Mgt.
pdf
This is an article showing an approach to database change managment that
will always work and gives you a complete audit trail on all database change
s
without slowing down the development of the database. It was the foundation
of the software called DB Ghost (http://www.dbghost.com)
How the database is developed (whether you use Query Analyzer, Enterprise
Manager or other third party tools) holds little relevance to the larger
problems of deploying those changes and having a clear picture of what has
changed, when it was changed, who it was changed by - so any problems can b
e
quickly solved in a repeatable and totally auditable manner. This (I believe
)
is the essence of database change management.
Excuse me for ranting, however I find it very frustrating watching the same
mistakes repeated time and again...
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating script
s
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is ofte
n
> suggested that you code your changes, by hand, using Enterprise Manager.
As
> I am looking at examples how to do this, I understand some of the basics o
f
> doing this. For instance, if I have a tabled called Numbers and there is
a
> field named Employee (data type int, length 4, non nullable) that I want t
o
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>

Creating Scripts via Query Analyzer or Enterprise Manager

I'm trying to decide what is the best practice in terms of creating scripts
that, for instance, update an existing database. It seems there are a
couple of ways to do this (although I am sure there are more). It is often
suggested that you code your changes, by hand, using Enterprise Manager. As
I am looking at examples how to do this, I understand some of the basics of
doing this. For instance, if I have a tabled called Numbers and there is a
field named Employee (data type int, length 4, non nullable) that I want to
add a unique constraint to, it seems that most examples have the following
simplistic syntax:
ALTER TABLE Numbers
ADD CONSTRAINT EmployeeUnique
UNIQUE(Employee)
If I make the same change via Enterprise Manager and then click the Save
Change Script button, the amount of code it creates is much more:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Numbers ADD CONSTRAINT
IX_Numbers UNIQUE NONCLUSTERED
(
Employee
) ON [PRIMARY]
GO
COMMIT
So, is all of this extra code necessary? Are the examples I am looking at
too simplistic? Is it OK to use EM to make changes and then generate the
scripts?
Thanks.
It si absolutely Ok to that in QA (the way you did).
All these SET statements, generated by EM, are actually happening whenever
you create a new session in QA, BUT behind the scene. You may see this if
you trace tsql exec from QA by Profiler.
With transactions is the same situation: in case of implicit transactions,
new T-SQL statement starts a new transaction. This explains Begin-commit
blocks inside EM-generated code.
All these settings are server defaults and can be changed.
Regards,
Marko Simic
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating scripts
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is often
> suggested that you code your changes, by hand, using Enterprise Manager. As
> I am looking at examples how to do this, I understand some of the basics of
> doing this. For instance, if I have a tabled called Numbers and there is a
> field named Employee (data type int, length 4, non nullable) that I want to
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>
|||Do you save all your change scripts so you have a complete audit trail of all
changes made to your database? How you make the changes is just one part of
database change management. Check out this article on the subject
http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf
This is an article showing an approach to database change managment that
will always work and gives you a complete audit trail on all database changes
without slowing down the development of the database. It was the foundation
of the software called DB Ghost (http://www.dbghost.com)
How the database is developed (whether you use Query Analyzer, Enterprise
Manager or other third party tools) holds little relevance to the larger
problems of deploying those changes and having a clear picture of what has
changed, when it was changed, who it was changed by - so any problems can be
quickly solved in a repeatable and totally auditable manner. This (I believe)
is the essence of database change management.
Excuse me for ranting, however I find it very frustrating watching the same
mistakes repeated time and again...
"epigram" wrote:

> I'm trying to decide what is the best practice in terms of creating scripts
> that, for instance, update an existing database. It seems there are a
> couple of ways to do this (although I am sure there are more). It is often
> suggested that you code your changes, by hand, using Enterprise Manager. As
> I am looking at examples how to do this, I understand some of the basics of
> doing this. For instance, if I have a tabled called Numbers and there is a
> field named Employee (data type int, length 4, non nullable) that I want to
> add a unique constraint to, it seems that most examples have the following
> simplistic syntax:
> ALTER TABLE Numbers
> ADD CONSTRAINT EmployeeUnique
> UNIQUE(Employee)
> If I make the same change via Enterprise Manager and then click the Save
> Change Script button, the amount of code it creates is much more:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.Numbers ADD CONSTRAINT
> IX_Numbers UNIQUE NONCLUSTERED
> (
> Employee
> ) ON [PRIMARY]
> GO
> COMMIT
>
> So, is all of this extra code necessary? Are the examples I am looking at
> too simplistic? Is it OK to use EM to make changes and then generate the
> scripts?
> Thanks.
>
>

Creating Reports Using SQL QUERY ANALYZER

I am a student of the University of Phoenix. In My SQL class I was unsuccessful in creating a report using basic SQL commands using the SQL Query Analyzer version 8.00.760.

I am using a version of Microsoft SQL Server 2000 Sold to me by the college. The Disk 1 said SQL Server 2000 Developer Edition. Disk 2 said SQL Server 2000 service pack 3a. The Third Disk Says SQL Server 2000 Reporting Services.

Since I am learning SQL on this platform I wanted to create reports using the SQL Query Analyzer. So how do I create reports using Basic Commands. This is what I have so far.

CREATE TABLE ACCOUNTS
(
Account_Number INT NOT NULL PRIMARY KEY,
Long_Description VarChar(500) NOT NULL,
Short_Description VarChar(500)NOT NULL,
Balance Money NULL,
);

Once I Imported the data from an excel file. I created a view.

CREATE VIEW Account_Report
(Account, Descript, Identifier, Balance) AS
SELECT Account_Number, Long_Description, Short_Description, Balance
FROM Accounts

Then from this view I pulled my report, and the best I could come up with was

SELECT *
FROM Account_Report
ORDER BY Account COMPUTE SUM(Balance)

I want to do more. Such as Create headers, Justify Left right or center, FORMAT Money Column to only have 2 decimal places, Trim the extra space on the right side of the columns, rename the columns, and scroll down 20 lines at a time.

Any help would be appreciated. My class is over so this is realy all just for the furthering of my own knowlegde.

Noctechie

if you are up to making professional reports you have make use of the reporting services

now for your needs, you have to integrate function in your select statement

here some to start with

String Functions

The following table contains samples of string functions. For more information, see String Functions and Using String Functions.

Function

Description

Example

LCASE( )¹,
LOWER( )

Converts strings to lowercase

SELECT UPPER(substring(lname, 1, 1)) +

LOWER(substring (lname, 2, 99))

FROM employee

Displays a last name after the first character is converted to uppercase and the remaining characters to lowercase.

LTRIM( )

Removes leading spaces from a string

SELECT stor_name, LTRIM(stor_address)

FROM stores

Displays an address column after extraneous spaces are removed from the front.

SUBSTRING( )

Extracts one or more characters from a string

SELECT SUBSTRING(phone,1,3)

FROM employee

Displays the first three characters (the area code) of a phone number.

UCASE( )¹,
UPPER( )

Converts strings to uppercase

SELECT * FROM employee

WHERE UPPER(lname) = 'SMITH'

Converts the contents of the lname column to uppercase before comparing them to a specific value (avoids mismatches if the search is case sensitive). For details about case sensitivity in SQL Server, see Query Designer Considerations .

¹ If calling as an ODBC function, use syntax such as: { fn LCASE(text) }.

Date Functions

The following table contains samples of date functions. For more information, see Date and Time Functions.

Function

Description

Example

DATEDIFF( )

Calculates an interval between two dates.

SELECT fname, lname, hire_date

FROM employee

WHERE DATEDIFF(year, hire_date, getdate()) > 5

Locates all employees hired more than five years ago.

DATEPART( )

Returns the specified portion of a date or datetime column, including the day, month, or year.

SELECT DATEPART(year, hire_date)

FROM employee

Displays only the year in which an employee was hired (not the full date).

CURDATE( )¹,
GETDATE( ) or DATE( )

Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today.

SELECT order_id

FROM orders

WHERE order_date = GETDATE()

Displays orders placed today.

¹ If calling as an ODBC function, use syntax such as: { fn CURDATE() }.

Mathematical Functions

The following functions are typical of those available in many databases. Refer to Mathematical Functions for more information.

Note You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM( ) to create averages and totals in your report. For details, see Summarizing and Grouping.

Function

Description

Example

ROUND( )

Rounds a number off to the specified number of decimal places

SELECT ROUND(qty * (price * discount), 2)

FROM sales

Displays a total price based on a discount, then rounds the results off to two decimal places.

FLOOR( )

Rounds a number down to the nearest (smallest) whole number

UPDATE titles

SET price = FLOOR(price)

Rounds all prices in the titles table down to the nearest whole number.

CEILING( )

Rounds a number up to the nearest whole number

INSERT INTO archivetitle

SELECT title, CEILING(price)

FROM titles

Copies the title and the price (rounded up to the nearest integer) from the titles table to the archivetitle table.

System Functions

The following functions are typical of those available in many databases. For more information, see System Functions.

Function

Description

Example

DATALENGTH( )

Returns the number of bytes used by the specified expression

SELECT DATALENGTH(au_lname + ', '

+ au_fname)

FROM authors

Lists the number of bytes required for the combination of last and first names.

USER( )¹,
USER_NAME( )

Returns the current user name

SELECT company_name, city, phone

FROM customers

WHERE salesperson = USER_NAME()

Creates a list of customers for the salesperson who runs the query.

¹ If calling as an ODBC function, use syntax such as: { fn USER() }.

Other Functions

The following functions illustrate utility functions available in many databases. For more information, see Functions.

Function

Description

Example

CONVERT( )

Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.

SELECT 'Hired: ' + CONVERT(char (11),

hire_date)

FROM employee

Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.

SOUNDEX( )

Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.

SELECT au_lname, au_fname

FROM authors

WHERE SOUNDEX(au_fname) = 'M240'

Searches for names that sound like "Michael".

STR( )

Converts numeric data into a character string so you can manipulate it with text operators.

SELECT str(job_id) + ' ' +

str(job_lvl)

FROM employee

Displays the job_id and job_lvl columns (both numeric) in a single string.

Creating Multiple Triggers is same sql script

I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GOWell that's the way to do it...(except for the SELECT * bit)

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Are you getting an error?

select * is dangerous btw.|||It stops at the second create trigger and indicates create trigger not valid. Should the syntax I have work?|||works for me...

USE Northwind
GO

CREATE TABLE PO_BOL(Col1 int)
CREATE TABLE receipt(Col1 int)
GO

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

DROP TABLE PO_BOL
DROP TABLE receipt
GO

Couple of things...lose SELECT *, Make sure you supply the column list for the insert...other than that it all looks good|||Thanks for the help! I got it to work..... Thanks again.

Another question:

I'm using VB to open a direct connection to SQL SERVER 2000. The AnsiNPW=off in the connection doesn't work. Any ideas? I had to create my tables in SQL server with SET ANSI_PADDING OFF to get the spaces trimmed.

Any thoughts?

JGS|||I'll tell anone who'll listen that I've forgot all my VB...otherwise they might make me build interfaces...

Just talking about it and I feel all dirty...

How is the table defined?

char or varchar??|||Your on to it! The fields are Varchar. I tried changing the fields to Char but AnsiNPW still doesn't seem to do anything in the connection string. Have you heard of any bug that AnsiNPW doesn't work?

creating DDL from a temporary table

I need to write a DDL Create script for a table in TempDb.
In Query Analyzer, I can see the table, but when I right click on the table
and attempt to script the Create I get a message telling that it could not
find the object in the collection (and it suggests I may want to use
qualifiers).
In Enterprise Manager, I can not see any user talbes in TempDb.
How do I get the scripts, ie, the DDL Create syntax...besides writing it
myself.
I have about 30 views which need to be changed to tables
Thaniks
Materialize the table, then script it:
Select * INTO SomeMaterializedTable
From #YourTempTable
Where 1= 2 --To have no rows, only the structure
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Abroit" wrote:

> I need to write a DDL Create script for a table in TempDb.
> In Query Analyzer, I can see the table, but when I right click on the table
> and attempt to script the Create I get a message telling that it could not
> find the object in the collection (and it suggests I may want to use
> qualifiers).
> In Enterprise Manager, I can not see any user talbes in TempDb.
> How do I get the scripts, ie, the DDL Create syntax...besides writing it
> myself.
> I have about 30 views which need to be changed to tables
> Thaniks

creating DDL from a temporary table

I need to write a DDL Create script for a table in TempDb.
In Query Analyzer, I can see the table, but when I right click on the table
and attempt to script the Create I get a message telling that it could not
find the object in the collection (and it suggests I may want to use
qualifiers).
In Enterprise Manager, I can not see any user talbes in TempDb.
How do I get the scripts, ie, the DDL Create syntax...besides writing it
myself.
I have about 30 views which need to be changed to tables
ThaniksMaterialize the table, then script it:
Select * INTO SomeMaterializedTable
From #YourTempTable
Where 1= 2 --To have no rows, only the structure
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Abroit" wrote:
> I need to write a DDL Create script for a table in TempDb.
> In Query Analyzer, I can see the table, but when I right click on the table
> and attempt to script the Create I get a message telling that it could not
> find the object in the collection (and it suggests I may want to use
> qualifiers).
> In Enterprise Manager, I can not see any user talbes in TempDb.
> How do I get the scripts, ie, the DDL Create syntax...besides writing it
> myself.
> I have about 30 views which need to be changed to tables
> Thaniks

creating DDL from a temporary table

I need to write a DDL Create script for a table in TempDb.
In Query Analyzer, I can see the table, but when I right click on the table
and attempt to script the Create I get a message telling that it could not
find the object in the collection (and it suggests I may want to use
qualifiers).
In Enterprise Manager, I can not see any user talbes in TempDb.
How do I get the scripts, ie, the DDL Create syntax...besides writing it
myself.
I have about 30 views which need to be changed to tables
ThaniksMaterialize the table, then script it:
Select * INTO SomeMaterializedTable
From #YourTempTable
Where 1= 2 --To have no rows, only the structure
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Abroit" wrote:

> I need to write a DDL Create script for a table in TempDb.
> In Query Analyzer, I can see the table, but when I right click on the tabl
e
> and attempt to script the Create I get a message telling that it could not
> find the object in the collection (and it suggests I may want to use
> qualifiers).
> In Enterprise Manager, I can not see any user talbes in TempDb.
> How do I get the scripts, ie, the DDL Create syntax...besides writing it
> myself.
> I have about 30 views which need to be changed to tables
> Thaniks