Generating Foreign Key Script

Jul 20, 2005

We sometimes get very large databases that we want to cut down to use for
testing.

The information is all related to a central accounts table.

The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.

After this I will restore the constraints back to their original state.

I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.

For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.

My idea was to go through the database saying:

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade

delete various accounts and related data

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)

Can anyone assist?

Thanks

View 1 Replies


ADVERTISEMENT

Generating Script To Show The Foreign Keys

Apr 19, 2008



hello

I'm using SQL SERVER 2000 (I even tried this using SQL SERVER 2005 Express)

I've a table tblA and table tblB.

tblB primary key is referred in tblA as foreign Key.

When I tried to create script....

Drop script is deleting the relation of tblA_tblB foreign Key...

But while creating the script of tblB...Its not creating the relation script.

How could I get the script with all the relations in a particular table.

plz help me out.

View 1 Replies View Related

Generating Numbers In SQL

Jan 19, 2004

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.


Any help would be highly appreciated.

thanks,
-sriram

View 7 Replies View Related

Generating SQL Script

May 31, 2001

Hi all,

I have generated an SQL script such that when it is run, it will automatically create tables if not existing or drop the existing tables. The problem I have is that, is there any way I can also make the script create not only the tables but also transfer the data from the old database to the new database?

I would really appreciate if any of you could point me to any existing programs, scripts etc that can do that. Thanks in advance

Regards,
Celia

View 1 Replies View Related

Generating SQL Scripts

Oct 8, 1999

Is there a way to generate SQL Scripts from a stored procedure?

For disaster recovery and documenation reasons, we'd like to create scripts of our databases periodically. I, being somewhat lazy, would like to automate this so I really don't have to remember to run this every so often.

Any help would be appreciated.

View 1 Replies View Related

Generating SQL Scripts

Jun 7, 1999

Is there a way to generate SQL scripts from other than SQL Enterprise Manager?

View 3 Replies View Related

Generating Nested XML

Oct 29, 2014

I must produce an XML file with this layout:

Code:
<root>
<dataroot>
<Professions>
<Profession>web designer</Profession>
<tags>
<tag>Ruby on Rails</tag>
<tag>Apache</tag>
<tag>HTML/CSS</tag>

[code]...

I can't get my head around it ..I've even tried to solve it by using FOR XML RAW.

View 3 Replies View Related

Generating A Leading Zero

May 13, 2008

Hi

I am unable to see how to generate a leading zero.



Table A

declare @TableA table ( ID numeric ,
Fruits varchar(10)
)
insert @TableA
select 1,'Oranges'
union all select 2,'Mangoes'
union all select 3,'Apricots'


ID Table A
1 Apples
2 Oranges
3 Grapes
4 Apricots


declare @TableB table ( seed numeric ,
)
insert @TableB
select 080513000448
union all select 080513000449
union all select 080513000450

Table B
seed
080513000448
080513000449
080513000450


I wrote the following query but i need generate a leading zero not sure which function can help maybe the right function but i am not sure how to use it in this case


SELECTconvert(varchar(10), getdate(), 12) +
(SELECT
CASE
WHEN SUBSTRING(ISNULL(max(seed),'00000'),1,6) = convert(varchar(10), getdate(), 12)
THEN SUBSTRING(ISNULL(max(seed),'00000'),7,12)
ELSE '000000'
END AS SEED
FROM B)
+ (row_number()
over (order by Id))
as SEED
FROM A



SEED
----
80513000451



The output which i need is

SEED
----
080513000451

rather then

SEED
----
80513000451

regards
Hrishy

View 20 Replies View Related

Generating Numbers

May 12, 2007

A simple question! I would like to create a database that creates say serial numbers and saves them to a table. If poss I would like to create them on demend and not duplicate.....

I have blank page syndrome!!!!

Thanks in advance

View 2 Replies View Related

Generating A Cube ..

Feb 19, 2008

Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.

Thank You

View 2 Replies View Related

Generating Sequence

Mar 15, 2008

Guys,

I have to generate sequence for distinct group of values for example

intially seq is set to 1 through out the table

categorydescidseq
__________________________________
AccountingAccounting61
AccountingAccounting72
AccountingFinal81
AccountingFinal92
AddendumAddendum 101

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

View 4 Replies View Related

Generating The Csv Files

Mar 31, 2008

hi

how can we generate the data in csv files of database tables by using sql query?


regards

View 3 Replies View Related

Primary Key Generating

Aug 24, 2006

Help again please,I need to insert rows into a table from another table. The tables areidentical column wise except the table im inserting from does not havea primary key value. On insert I need to generate a primary key thatis consecutive based on the table im inserting into. Also the table iminserting into does not have a identity column. Much appreciated.

View 7 Replies View Related

Generating Packages Using C#

May 12, 2006

Hi,

we would like to generate SSIS packages(connections, data flow tasks, etc..) programatically using C#. Is it possible to do so?

At present we are creating packages by dragging and dropping required componets from toolbox and configuring them manually. We have a requirement of creating around 300 packages every week. Manual creation of packages is really a nightmare.

I heard from one of friends that using c# we can create packages. is it true? if so, can we view/edit the packages generated using c# using VS 2005 IDE(if required) ?



Regards,

Gopi



View 4 Replies View Related

Generating Reports On Fly

Mar 29, 2007

Hello,



SRS 2005 provides functionality for loading and rendering reports on fly using LoadReportDefinition and Render methods defined in ReportExecutionService webservice.



I was wondering if the same/similar behaviour can be accomplished in Reporting Services 2000.



Thanks in advance,



Kobi

View 1 Replies View Related

Generating Script

Jul 12, 2007

What does N indicate in the following script

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].xxxx' )

Plz can anyone explain

EXEC dbo.sp_executesql @statement = N'Create View xxxxx'

View 4 Replies View Related

Generating Sql Logins

Oct 17, 2006

I've been given an excel file that lists:

userID's,
Names
Password
expiary dates.

I need to convert this list into sql server logins. Can this be done via a tsql statement? if so how, and if it cant be done via a statement then what other way can it be done.

Many thanks in advance.

View 2 Replies View Related

Generating A Primary Key

Sep 18, 2007

I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.



What are my other options? How can I generate a primary key for every new row?

Any advice is appreciated.
Regards
Sara

View 6 Replies View Related

Generating CREATE Scripts

May 20, 2008

Hello all,
I have a few questions relating to scripts and I'm looking for some help, or at least a point in the right direction.
1) I'm generating CREATE scripts to move a number of tables/procedures/functions from my testing database to my production database.  The issue I have is the script doesn't seem to be generated in a particular order, so I'm getting errors when a table/procedure/function is created that references something that hasn't been created yet.  Is there a setting available to create the script in a specific order to prevent these errors?
2) This is somewhat related to the first question.  I need to drop all the tables/procedures/functions before I re-create them.  Can I set all the DROPs at the beginning of the script, and then run all the CREATEs?
3) Eventually the database will be ready for production and in use.  And eventually I'll be asked to make changes, without erasing existing data.  Can I accomplish this using the generate scripts feature?  What topics should I be investigating when looking into conducting these types of updates?  "Updating database" is too general and I'm not sure the technical term.  I've done very simple DB-related tasks so I'm trying to figure out what I'm looking for so I can educate myself.  Basically, I don't know what I don't know.
Any help is appreciated.  Thanks.

View 1 Replies View Related

Generating Tables Programatically

Jun 7, 2008

hi i am trying to gernerate some tables ( first with a script and then with a stored procedure),, in ms server management studio exrress 2005 using the followingcreate table ip(    id int IDENTITY NOT NULL,    ip varchar(15) NOT NULL,    hostname varchar(128) NOT NULL,    primary key(id)) ; i store this in a new query (assume :right click associates the query to the current database ?) and run it and nothing happens  its been a while since i did sql but i think the sql is ok....how or more specifically where is the appropriate place to run this code  thankssimo  stored as a new query     

View 1 Replies View Related

Generating Text File

Dec 4, 2003

I have a table(say tblUserInfo) on SQL Server. What I like to do is a text file will be generated on the hard drive which SQL Server sits on when a new record is inserted into tblUserInfo. The content of the text file comes from the table. Is there any way we can go for doing that?

View 2 Replies View Related

Generating Membership Number

Jan 20, 2005

Hi all,
I have a question about generating membership numbers on the fly when someone registers to my website.

Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...

SR357324J

This will then stay with them for the lifetime of their membership and be on their printed loyalty card.

My questions are...
1) Is there a 'good practice' for membership number format and generation?

2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.

I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.

Thanks for your contribution.

View 2 Replies View Related

Generating A Database Design From Xml

Apr 21, 2006

Hi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/

(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)

But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#

Does anyone know how to do this?

Cheers,

Matt.

View 1 Replies View Related

Auto Generating An Email

Jun 5, 2006

Hi All,
I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am)
Is there anyway to achive this via SQL 05 Express?
Thanks,
Mal.

View 4 Replies View Related

GENERATING A LIST OF DATES

Jan 25, 2002

Can someone show me how to load a list of dates into a table.
(i.e. from Jan. 1, 1995 to Jan, 1, 2005)

Thanks in advance!
BV

View 1 Replies View Related

Generating Serial Codes

Jul 29, 2001

Hi,

I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.

View 1 Replies View Related

Generating Surrogate Key Without IDENTITY

Jan 22, 2001

Hello
I'm looking for a way of generating the next key value that works in MS and Sybase SQL Servers. Sybase identity columns are a bit dodgy, so...

If I have a separate table NextKey (NextKey int) with one row that I update as follows...

declare @NextKey int
update NextKey set NextKey = NextKey + 1, @NextKey = NextKey + 1
insert into myTable (PrimaryKeyCol, ....) values (@NextKey, ....)

are there any problems with concurrency ? As I see it the update will lock the row so different connections will always come up with a different @NextKey value....

Thanks
John

View 2 Replies View Related

Generating SQL Script Of Database

Jan 3, 2000

Hi,
is there a way in SQL Server 7, to use some sql to extract the entire database schema?
I know that with the Enterprise manager there is All Tasks- Generate SQL Scripts.
But I need to connect to the database and use some sql to do this.
Any suggestions?

Thankyou
RH

View 1 Replies View Related

Generating Scripts For An Application

May 8, 2002

Hi there..

We are developing an INtranet application using SQL Server 2000 and ASPs, we are looking for a way to Generate a Module wise Scripts(Database Scripts like Table Scripts / Stored Procedure / Triggers / Functions etc).

This facility exits in Oracle 8i. How can i do the same in SQL Server2000..??

When i say Application..I mean Our Application and When i say Module..I mean one of the Modules in the Application..

Does SQL Server 2000 have any utility to do the same..???

If I am able to do this..then the COde Maintainablility will be easier...

Thanx

regards
Murali

View 1 Replies View Related

Tool For Generating StoredProc

Jul 9, 2001

Hi,

I'm using SQL Server 2000 as our back end. I'm finding it bit difficult to write StoredProcs manually to be called from my front end. Is there any good Stored Proc generator tool available?

Thanks,
Harish

View 1 Replies View Related

Generating Sequence Number

Aug 29, 2001

Hello,

I need to know how to generating a sequence number, for example, from 300,000 to 900,000 without skipping any number due to failure. For example, if user 1 request a number then he/she will get 300000 in a transaction. User 2 will get 300001. How ever user 1's transaction fails, then the next request should get 300000. Is it possible to do this in SQL2K? If so, how do I create a table that and stored procedure that can do this.

Thank you so much.

NK

View 3 Replies View Related

Generating Sql String For Execution

Sep 6, 2004

I've a need to generate a condition clause (if statement) from 3 variables: a value, an operator & another value.

To generate this: 'abcd' like 'a%'
& verify if the condition is satisfied I've done the following:

CREATE TABLE #Temp(Result varchar(10))
declare @cond1 as varchar(50), @op as varchar(4), @cond2 as varchar(50),
@expr as varchar(50),@result as varchar(10)
set @cond1 = '''abcd'''
set @op = 'like'
set @cond2 = '''a%'''
set @expr = @cond1 + ' ' + @op + ' ' + @cond2
insert into #temp exec ('select case when ' + @expr + ' then ''true'' else ''false'' end')
if exists (select result from #temp where result = 'true')
select 'it is true'
else
select 'it is false'
drop table #temp


It works, but a bit clumsy. Is there a more elegant way to do this ?

View 3 Replies View Related

Using Sql To Output Xml - Generating Error

Jul 19, 2006

Hi guys i'm a newbie to this forums so hopefully i've posted in the right place, so here goes .....

My company has been given a CMS to look at, thing is everything seems to be working except for this beasty stored procedure.. the purpose of this stored procedure is to gather data and output it as xml, but for some reason and i havent a clue why - i am getting an error stated below - i have also attahced the stored procedured in hopes of some kind gurus can help me

Appreciate any and all help guys
andy

ERRROR MESSAGE:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

View 2 Replies View Related







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