What Is Right Way To Generate Create Table

Aug 30, 2001

Hello everybody
I am doing update on Database using Erwin
Is any diffrence in this code and what is is right way to create table in my case ?
-- --- this code generated by Erwin 3.5.2
CREATE TABLE obooGrpAllotRmBooking (
BookingDate ASmallDateTime NOT NULL,
ID_RoomType ID NOT NULL,
ID_GrpAllot ID NOT NULL,
ContractQty hAvailQty DEFAULT 0
)
go


ALTER TABLE obooGrpAllotRmBooking
ADD PRIMARY KEY (ID_GrpAllot, ID_RoomType, BookingDate)
go




------------- This code generated by SQl server 7 generate script ---
CREATE TABLE [dbo].[obooGrpAllotRmBooking] (
[ID_GrpAllot] [ID] NOT NULL ,
[ID_RoomType] [ID] NOT NULL ,
[BookingDate] [ASmallDateTime] NOT NULL ,
[ContractQty] [hAvailQty] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[ID_GrpAllot],
[ID_RoomType],
[BookingDate],
[ContractQty]

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD
CONSTRAINT [DF__obooGrpAl__Contr__78DED853_05] DEFAULT (0) FOR [ContractQty]
GO

Thanks

View 1 Replies


ADVERTISEMENT

Generate Create Scripts

Nov 29, 2007

Hi there,
 I have made a stored procedure to create a script with a create statement for a table. it looks like this:
Create Procedure GenerateScript ( @tableName varchar(100)) as If exists (Select * from Information_Schema.COLUMNS where Table_Name = @tableNameBegin declare @sql varchar(8000) declare @table varchar(100) declare @cols table (datatype varchar(50)) insert into @cols values('bit') insert into @cols values('binary') insert into @cols values('bigint') insert into @cols values('int') insert into @cols values('float') insert into @cols values('datetime') insert into @cols values('text') insert into @cols values('image') insert into @cols values('uniqueidentifier') insert into @cols values('smalldatetime') insert into @cols values('tinyint') insert into @cols values('smallint') insert into @cols values('sql_variant')
set @sql='' Select @sql=@sql +case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type + case when Data_Type in (Select datatype from @cols) then '' else '(' end +case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+ ','+case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end +case when Data_Type in (Select datatype from @cols)then '' else ')' end +case when Is_Nullable='No' then ' Null,' else ' Not null,' end from Information_Schema.COLUMNS where Table_Name = @tableNameselect @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where Table_Name = @tableNameselect @sql=@table + substring(@sql,1,len(@sql)-1) +' )' select @sql as DDL End Else Select 'The table ' + @tableName+ ' does not exist'GO
 
Now my problem. I want to install this SP in the master db and i want call other databases. With  "SP_RENAME" that's possible when i say "USE otherdatabase". With my own stored procedure it's not possible. What do i have to alter?
Thanks

View 3 Replies View Related

Create Procedure Or Trigger To Auto Generate String ID

Feb 20, 2004

Dear everyone,

I would like to create auto-generated "string" ID for any new record inserted in SQL Server 2000.

I have found some SQL Server 2000 book. But it does not cover how to create procedure or trigger to generate auto ID in the string format.

Could anyone know how to do that?? Thanks!!

From,

Roy

View 7 Replies View Related

How Do I Move My Database Schema From 1 Machine To Another? (generate Create Script Is Greyed Out)

Jan 15, 2005

how do I move my database schema from 1 machine to another? Its a MSDE 2000 DB and Im using visual studio 2003.

In VS when I goto the server explorer and right click on a DB the "generate create script" options is greyed out.

What am I doing wrong?


If you cant help me with the above question could you please recommend another way for me to get my DB schema with all its constraints from one of my work stations to another. I dont want to manually recreate it.

Thankyou

View 3 Replies View Related

In The Server Explorer Generate Create Script Is Greyed Out, Whats Wrong?

Jan 14, 2005

In the server explorer "generate create script" is greyed out, what am I doing wrong?

I want to generate a script that will re-create my DB schema on another machine.

Any help would be appreciated.
Thanks

View 3 Replies View Related

Generate Table Scripts

Jul 28, 2007

HiI'm developing a website using SQL Server Express 2005 and VIsual Web Developer Express, and now want to start testing my website in a live environment. Until now I have been manually creating tables on my web database to match those on my local DB, but I have so many tables I don't want to manually recreate them all again. Is there a method in SQL Server Express 2005 that will allow me to generate insert scripts for  all the tables? CheersAndy 

View 3 Replies View Related

How To Generate An ID And Fill It In The Table

Feb 29, 2008

hi i have a table with Id and otehr fields,ID is autogenerate with datatype int.
But now i want to generate an Id in AU001 format and then want to insert into table where it must be autogenerate.How to do this.
Please help me with the problem,its very urgent....

View 6 Replies View Related

How To Generate DDL Of An Existing Table

Feb 25, 2007

I need sql script that can generate 'Create Table' definition including primary and foreign constraints for the table already existing in SQL server 2000.

Please do not refer me to the utilities or commercial software, I need only the sql script.



thanks

View 5 Replies View Related

How Generate SID On Users Table

Nov 12, 2007

Hi,
How can I generate the user SID on ReportServer.dbo.Users table?
Tks

View 1 Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

May 3, 2004

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View 3 Replies View Related

SQL Server 2008 :: How To Generate DDL For Table

Sep 11, 2015

I am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...

View 3 Replies View Related

Generate SQL Script With Table Data

Aug 24, 2005

Hi


i have one problem in database to creating SQL script

i want to copy table strucure with table data


Pls help


Shailesh Kavathiya
http://www.codegroups.com/blog/

View 9 Replies View Related

How DO I Generate A Table Of Contents For A Report??

Aug 28, 2006

Hi!!!

Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.

View 10 Replies View Related

How To Generate Table Structure For Ssis

Jan 7, 2008

I'd like to know more about data warehouse. I've known some for SSIS working with SSAS. But it's existing data warehouse. I just transfer data to data warehouse using bussiness rules. I have no idea about data warehouse, how to design data warehouse from transactional database, how to generate the table structure. Any tips or books for that? Thank you very much.

View 5 Replies View Related

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP

Aug 4, 2004

Hi All,

I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

Create PROC dbo.GrantPermission
@user1 varchar(50)

as

Grant create table to @user1
go

Grant create view to @user1
go

Grant create Procedure to @user1
Go



Thanks Guys.

View 14 Replies View Related

Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}

May 20, 2008

the subject pretty much says it all, I want to be able to do the following in in VB.net code):
 
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
 
How would I do this?

View 3 Replies View Related

Generate Identity Field On Existing Table

May 11, 2004

Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?

Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.

Thanks in advance!

View 3 Replies View Related

How To Generate Random Default Value For A Field Under Sql Table?

Oct 13, 2005

hi
I have a field call: password, I want to generates some random chars as
default password for users (so user got something to login, and update
later).

Current Solution I have:
I made myself a trigger and it does generate password on insert, but
the problem with that is I have to allow NULL on that field.  I
don't want NULL to be allowed, and if I don't allow null... sql server
won't allow me to insert a record (when leaving password field empty).

Reason i don't wanna allow NULL on that field, is because there may be
more applicaions using the same field from the same database. 
Just in case if the other developer gets sloopy, I don't want other
application(s) allow user put to enter a null or leave it blank on my
password field.

My question is:
1) can I put a function or some sort at the "Default" field under "Design Table", if so...how?
2) any suggestions for a better implementation?
3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?

TIA

View 5 Replies View Related

Script To Generate INSERT Statements On Table

Jul 20, 2005

I'd I have a problem I'd like to post CREATE TABLE and INSERT statementsthat will create my table and insert data into the table.I can use the scripting feature in Enterprise Manager to generate CREATETABLE scripts.Is there a script I can run that will generate INSERT statements so I caninclude sample data.Thanks

View 1 Replies View Related

Transact SQL :: Generate Drop Table Statements

Oct 13, 2015

I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?

View 3 Replies View Related

Transact SQL :: Only Generate Email If Table Contains Data?

Jul 1, 2015

I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table.  My syntax fires off an email even when the table does not contain data, it just sends a blank email.  this will only generate an email if teh table contains data?  

if exists (Select from FailedJobs)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail'
@recipients = 'asdfasdfsdf@aafas.com'
@from_address = 'asdfasdfacasca@cc.com'
@query = 'Select * from failedjobs'
@subject = 'List Of Failed Jobs'
@attach_query_result_as_file = 1;

View 3 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

Can CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?

Jul 20, 2005

I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND

View 2 Replies View Related

How Can I Add A Unique Key Column To A Table And Generate Its Values Automatically?

May 2, 2006

Hi, all,

I have a question about adding a unique key column to an existing table.

what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?

Thanks a lot in advance for any guidance.

View 6 Replies View Related

Transact SQL :: Generate A Report Based Of Columns In Each Table

Oct 31, 2015

I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different. 

Eg:

Table 1 has Account Count

Table 2 has Account Count

Table 3 has Account Count

Table 4 has Account Count

Table 5 has Account Count

I want output as: 

Account Count Account Count Account Count Account Count Account Count 

View 4 Replies View Related

Transact SQL :: Method To Generate Insert Statements For Data In A Table

May 30, 2013

I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)

View 7 Replies View Related

DB Design :: Generate A Script To Choose Only Top 10 Items From Each Table In Database?

Aug 22, 2015

I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements? These top 10 should be ordered by the primary key.

View 4 Replies View Related

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE

Nov 21, 2006

For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View 6 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

T-SQL (SS2K8) :: Generate Script For View Dependencies Of User Defined Table Types?

Aug 25, 2014

T-SQL script for the following request ?

Generate script for view dependencies of user defined table types ?

View 2 Replies View Related

SQL Server 2012 :: Query To Generate Relationship (Parent Child Hierarchy From A Table)

Jul 18, 2015

I am working on a query to generate parent child hierarchy from a table.

Table has below records.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable

[Code] ...

how to achieve this.l tried with temp tables it doesn't work.

View 5 Replies View Related

Create INDEX Within CREATE TABLE DDL

Jan 27, 2006

Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance

View 2 Replies View Related

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

Feb 11, 2015

i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?

--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END

only on calling the proc does this give an execution error

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved