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
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
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.
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
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....
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.
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)?
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...
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.
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.
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:
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.
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?
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
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?
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;
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.
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
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?
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.
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?)
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.
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.
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
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