Truncate Tables Based On Foreign Key Relationships

Nov 5, 2007

Guys,

I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.

For example

EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table

My truncate script should be

TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE

IS there any automated way to figure out parent and child tables and generate truncate script for the same.

Thanks

View 3 Replies


ADVERTISEMENT

Truncate Database Tables Based On Foreign Key Constraints

Nov 5, 2007

Guys,

I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.

For example

EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table

My truncate script should be

TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE

Is there any automated way to figure out parent and child tables and generate truncate script for the same.

Thanks

View 1 Replies View Related

Should I Define Foreign Key Relationships

May 10, 2007

I've always created database tables with one Primary Key that increments by one for each new record.  I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys). How helpful is it to define these Keys?  I assume it helps make the database more efficient but is it necessary if you're working with small databases? 

View 1 Replies View Related

Setting Up Foreign-key (FK) Relationships In VWD?

Apr 12, 2008

 I working a tutorial where I will learn a lot of new stuff, LINQ being one of them. As a part of this tutorial, there are four tables and foreign keys. My db skills were limited to simple tables and i've nevr done anything where I related tables before. I'm a bit confused about foreign keys and how to set them up. I think I'm setting up right, but I have a doubt. I have a couple of screen caps to show you what where my doubt comes from at:
 http://www.noelakins.com/keys.htmlWhat is causing my confusion is that the connecting lines between the Albums and Events table diagrams in my setup seems to point back to the Photos table, and the Photos table seems to point to the Tags table. From the tutorial, the connecting lines seem to be pointing the other direction. Am I setting up the key right, or do I have them backwards?  I'm setting out to learn a lot of stuff in this tutorial, so when I mess it up, i would like to know that it isn't because the foreign keys are wrong.EDIT:After doing some googling and some reading and watching, I'm a little less confused about how to set up foreign keys, at least how to do it in VWD. My problem now seems to be understanding the relationships. In the tutorial example, the Tags table has a FK on the PhotoID field, which matches the primary key (PhotoID) in the Photos table. Now, does this mean that the Tags table is the parent table and the Photos table the child?  If this is true, then the other two tables must be child tables of the Photos table. At least that how I think this should work. The issue that I'm having with this is trying to understand how a cascade  delete would work. Is the foreign key relationship a one way or both way relationship? For example, if I have made a foreign key on the AlbumID field of the Photos table, where the primary key table is Albums, PK is AlbumID and the foreign key table is Photos, then, if I do a cascade delete of a particular photo, does it delete the album too? That wouldn't make sense. Foreign keys and cascade deletes must be one way, right? Thanks 
   

View 2 Replies View Related

Edit Foreign Key Relationships

Feb 5, 2007

Hi,

I'm using SQL Server 2005 Management Studio. I've created several Foreign Key Relationships using Database Diagrams. Now I'm trying to edit the constraints on the relationships, but when I right click on the relationship, the only option available is "Delete Relationships from Database". But I am not able to edit the relationship. Does anyone know how to do this?

Thanks for the help.

View 1 Replies View Related

Update Row With Foreign Key Relationships

Mar 3, 2008

Hi,

I have 2 tables Publication and Advertisment_Codes. I want to update the publication_code in both tables where country_id=1 and publication_code='A'. However, it gives me an error because of foreign key relationship. I tried first updating in Advertisment_Codes and then in Publication table but it didn't work. I tried the other way round but also it didn't work.
How can I update the publication code?
Thanks


CREATE TABLE [dbo].[Advertisment_Codes](
[Advertising_Code] [nvarchar](20) NOT NULL,
[Country_id] [int] NOT NULL CONSTRAINT [DF_Advertisment_Codes_Country_id] DEFAULT ((0)),
[Publication_code] [nvarchar](20) NULL,
[Advert_type] [nchar](1) NULL,
[Date_Entered] [datetime] NULL,
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Upd] DEFAULT ((0)),
[Default_Code] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Default_Code] DEFAULT ((0)),
[Active] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Active] DEFAULT ((0)),
CONSTRAINT [PK_Advertisment_Codes_1] PRIMARY KEY CLUSTERED
(
[Advertising_Code] ASC,
[Country_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Countries] FOREIGN KEY([Country_id])
REFERENCES [dbo].[Countries] ([Country_id])
GO
ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Countries]
GO
ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Publications] FOREIGN KEY([Country_id], [Publication_code])
REFERENCES [dbo].[Publications] ([Country_id], [Publication_code])
GO
ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Publications]

CREATE TABLE [dbo].[Publications](
[Country_id] [int] NOT NULL,
[Publication_code] [nvarchar](20) NOT NULL,
[Publication_name] [nvarchar](50) NOT NULL,
[Media_type] [int] NOT NULL CONSTRAINT [DF_Puclications_Media_type] DEFAULT ((0)),
[Date_Entered] [datetime] NULL,
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Publications_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Publications_User_Id_Upd] DEFAULT ((0)),
CONSTRAINT [PK_Puclications] PRIMARY KEY CLUSTERED
(
[Country_id] ASC,
[Publication_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Publications] WITH CHECK ADD CONSTRAINT [FK_Publications_Countries] FOREIGN KEY([Country_id])
REFERENCES [dbo].[Countries] ([Country_id])
GO
ALTER TABLE [dbo].[Publications] CHECK CONSTRAINT [FK_Publications_Countries]

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 2 Replies View Related

Updating Data In Foreign Key Relationships

Aug 1, 2006

Hey guys this is a SQL Server 2005, .net 2.0 question. I got 3 tables: Job, Times, Employee. The times table is related to Job and Employee by foreign keys. The Times table is pretty much a table where the employee's time logs are kept for a specific job. Since there was a M:M relationship between Job - Employee, I decided to create a Times table in between, and the primary key of this table should actually be the PK of Job and the PK Employee, but I couldn't figure out how to make a PK consisting of two fields.
Anyhow, my main question is: How to assign an Employee to a specific job in .net? In other words, what's the best way of retrieving the Job_id and Emp_id and sticking them in the Times table? I was doing a form with a drop down for Emp_id and Job_id, but I don't know how to do the inserts... I'm confused and would appreciate any help.THANKS!

View 6 Replies View Related

How Do I Move Data And Preserve Foreign Key Relationships

Feb 21, 2005

How do I move data from one server and append to the same table structure on a second server and preserve the foreign key relationships. In other words TableB.TableA_ID references TableA.ID. The IDENTITY ID numbers themselves will change but they must continue to match up on the destination server the way they did on the source server.

I can do this using procedural programming but that is usually not the way to go.

(btw, this is a cleaned up version of a question I tacked on to another thread)

View 7 Replies View Related

How To View Foreign Key Relationships In Management Studio?

May 15, 2008



I cannot find a good way to view existing Foreigh Key Relationships in SQL Server Management Studio Express. In the Object Explorer, if you click on a table to expand it, you can see a tree view of EVERYTHING about the table (Columns, Keys, Contraints, Triggers, Indexes, Statistics), but NOTHING about Foreign Key Relationships!!

The only way I have found is that you have to open the table in Design mode, then right click, and choose Relationships, and only then can you see what FK Relationships are defined.

You should be able to see the FK Rletaionships in the tree view of the Object Explorer window!

View 3 Replies View Related

Alter Statement To Create Foreign Key Relationships

Jul 18, 2006

Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?


ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [Category] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE

View 3 Replies View Related

SSMS Setting Relationships (Foreign Keys)

Jul 6, 2007



Hi,



I received the error below upon saving when I was trying to create a foreign key in a table using relationships in "table designer" of sql server 2005 management studio. This process has been successful for other tables. What might be the problem?



Thanks






Code Snippet

- Unable to create relationship 'FK_SAMPLE_GRID'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_GRID". The conflict occurred in database "acquire", table "dbo.GRID", column 'GRIDNAME'.

View 1 Replies View Related

Truncate And Foreign Keys

Aug 25, 2006

Hello all,

Simple question. I have two table with a foreign key in between.

Then if I want to truncate the tables, of course this is not possible because of foreign key. But with the following code, this should work:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

truncate table Table_1

truncate table Table_2

GO

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

But I still have the error

Msg 4712, Level 16, State 1, Line 2

Cannot truncate table 'Table_2' because it is being referenced by a FOREIGN KEY constraint.



What is wrong ?

You will find below the code for the creation of my two tables

Thanks for your help





CREATE TABLE [dbo].[Table_1](

[ID] [int] NOT NULL,

[foreignId] [int] NOT NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Table_2](

[Id] [int] NOT NULL,

CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table_1] WITH NOCHECK ADD CONSTRAINT [FK_Table_1_Table_2] FOREIGN KEY([foreignId])

REFERENCES [dbo].[Table_2] ([Id])

GO

ALTER TABLE [dbo].[Table_1] CHECK CONSTRAINT [FK_Table_1_Table_2]

View 9 Replies View Related

DB Design :: Extracting Relationships From FoxPro Code And Generate Foreign Keys In Server

Aug 4, 2015

I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?

View 7 Replies View Related

Temporarily Drop FOREIGN KEY Constraints To Truncate A Table After Testing

Feb 21, 2007

 
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it?
Newbie

View 1 Replies View Related

Data Access :: Cannot Truncate Table Because It Is Being Referenced By FOREIGN KEY Constraint

Nov 11, 2015

Am working on sql database table shifting to new database tables. Am getting the following error while truncating the tables:-

Cannot truncate table 'orderItem' because it is being referenced by a FOREIGN KEY constraint.How can i remove all the items in the table if i found this error.

View 7 Replies View Related

Transfer Sql Server Objects Task Truncate Table Fails Due To Foreign Key

Aug 10, 2006

Hi

We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS.

I set my Transfer Sql Transfer Objects Task to use the following settings:

CopyData = true

ObjectsTocopy = All Tables

As soon as it hits a table that has a foreign key, I get the following error:

"Truncate data failed for Table nnn"

I've tried setting DropObjectsFirst = True but this fails too with foreign key violations.

First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations?

Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups?



Kind regards

Ben

View 3 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

Apr 11, 2006

Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View 1 Replies View Related

XML Into SQL Tables With Relationships

Aug 21, 2006

Hi,

I'm trying to find the most efficient method of importing XML files into SQL Server using SSIS. The objective is to read these files created in a source directory and potentially there will be a large number of these created every second.

I've experimented with the XML Source data flow task and been able to import using either the OLEDB Destination or SQL Server Destination.Using the associated XSD file

These methods work well when there are no PK or FK relationships, i.e. the target tables are stand alone.

However when I use a schema that has relationships I need to be able to import and be able to return the key then use this key to import into another table to ensure the FK relationship and so on.....

From what I know I will need to create multple Data Flow tasks when reading a single xml file then in turn create a 'look up' on a table to see if this entity exists and on error insert using the OLEDB command and then somehow return the PK generated???

Has anyone tried this? And would be happy to share there experiences?

It seems a lot easier to use an SP and then use an execute SQL task method

Your thoughts would be greatly appreciated

Thanks

John

View 13 Replies View Related

Cannot Truncate Table 'Database.dbo.Table' Because It Is Being Referenced By A FOREIGN KEY Constraint..

Aug 23, 2006

Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.

It will run and update with a primary key until I add a foreign key to another database.

Here is my error when running the package when table 2 has a foreign key.

[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

For What Are Good Relationships Between Tables?

Jan 25, 2008

hello,
i am wondering, for what i use relations between tables? if i make sure that the data inserted into the table matches, for what i need table relationships?
i have a table named Pictures with: PictureID, UserID, UserName, PictureName, Description...
do i have to make relationships between UserID from Pictures and UserId from aspnet_Users?
and the second table PicturesComments witch has: CommentID, PictureID, UserID, UserName...
do i have to makre relationship between PictureID from PictureComments and PictureID from Pictures AND between UserID from Pictures and UserId from aspnet_Users?
sorry for this stupid questions...
thanks

View 5 Replies View Related

Setting Up Tables And Relationships?

Mar 12, 2014

I am creating a small piece of software which handles a pre-advice data file, several scanner inputting weights and a supplier weight report.

The pre-advice contains a list of barcodes and details revolving around that barcode, each barcode will be scanned and a weight will be obtained, this will be place in the DB.

Currently my tables can be summarized like this.

Pre-Advice Table
Barcode

Lots of other info…

Scanners Table
Barcode
Date
Time
Weight

Which device input this information

Suppliers Weight Table
Barcode
Date
Time
Weight

Here is the process(es):

1.Pre-advice table populated.

2.When the barcode is scanned and a weight is obtained, a record will be added to the scanners table and this becomes the ‘active’ weight.If there is already an ‘active’ weight against this barcode the user will be asked if they wish to use the ‘active’ weight already in the system or update the ‘active’ weight to the weight they have entered.

3.Then when the supplier weight report is inputted into its table, should the ‘active’ weight be less than the supplier weight, the supplier weight becomes the ‘active’ weight.

At any given time a user can pull a report based on information in the Pre-Advice table, which will need to include the ‘active’ weight for that barcode’s record.

Additionally, no weights should be deleted, I need a fully traceable log of who, what, where & when. I will need to pull reports on the trials of scans so it really is a must to keep these.

Furthermore, if a scanner was to scan the same barcode after the Suppliers weight had been input and the user chooses to replace the weight then, the scanners weight should become the new ‘active’ weight.

I believe I will need to modify my tables to accommodate my needs, however I am not sure exactly what or how to set up what I need.I understand I will need some type of relationship between all of the tables to be able to pull the report on the Pre-Advice table, but I am lost.

how I will need to set up my tables/relationships in order to achieve my requirements?

View 3 Replies View Related

Relationships Between Tables In Different SQL Databases

Jul 27, 2007

There are several databases (currently in Access) that are being moved to SQL Server. Would prefer keeping those databases separate in SQL Server. How do you do something similar to Access's "link" capabilities and relate tables in different physical databases? For example, relate the authors of a document in a document database to persons in a people database where AuthorID in Docs.DocAuthors.AuthorID is related to People.Persons.PersonID

View 4 Replies View Related

Insert Into Tables With Relationships

May 15, 2008

Hi, I have two tables. One is called ContentRecord and it is one field, an identity field that is a primary key. I have another table that is has several fields including a field that is a foreign key called contentFK. I have set up a foreign key relationship between the tables. There is a one to many relationship. I would like to know the best way to add a record to both tables. The way I am thinking to do it is to do the insert statement to add the record to the ContentRecord which will return the value of the primary key field. Then do a seperate insert statement to insert the record into the second table where I set the contentFK equal to the returned value from the first statement. Does that make sense? Is there a better way to do this?

Thanks,
Laura

View 4 Replies View Related

Sorting Based On Foreign Key Feilds

Dec 1, 2006

shibu writes "I have a table 'manage_user_types' and its primary key is 'Category_ID'. There is another field 'Category_Parent_ID' which refers the 'Category_ID' in the same table. And one more field is 'Category_Name' which is related to 'Category_ID'. ie.when displaying the table in front end, 'Category_Name' is displayed under 'Category_Parent_ID'. I want to sort the table with 'Category_Name' when clicking the 'Category_Parent_ID' to sort. What should i do?"

View 1 Replies View Related

How Do I Transfer Tables And Relationships From One Database To Another?

Aug 24, 2006

Hi, I'm working on Sql Server 2005 Express and I'm trying to transfer over some of my tables with the primary and foreign keys relations as well as the data in them from one asp.net 2.0 website to another.But I can't seem to find the Import/Export option in Sql Server 2005 Express Edition, does anyone know how I can do this?Thanks in advanced.

View 1 Replies View Related

Using Code To Maintain Relationships Across Tables.

May 17, 2002

Can anyone give me an idea like, what percentage of organizations use 'code' to maintain the parent-child relations on their tables than
having FK constraints thru the db model? Because,all the companies that I worked with used 'code' to control the relationships across the tables(not the PK/FKs.!!)
Thanks.
Neil.

View 2 Replies View Related

Tables Export 'Success' But No Relationships...

May 29, 2003

I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.

I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....

thanks
David

View 9 Replies View Related

How To Implement One-to-one, One-to-many And Many-to-many Relationships While Designing Tables?

Mar 20, 2008



Can Anybody explain me this how to Do this ?

Is this done by definning the Primary key and foreign key constraints ?

View 1 Replies View Related

Query To Count # Records In Db Based On Foreign Key

Mar 14, 2008

My SQL is very basic.  How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
 SELECT     COUNT(*) AS Expr1, tblArticle.ArticleIDFROM         tblArticle INNER JOIN                      tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.

View 3 Replies View Related

Discover Relationships Between Existing Tables Of A Database

Mar 23, 2007

Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.

View 2 Replies View Related

Creating Tables In SQL Server Via Query Analyzer With Relationships

Jun 7, 2005

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

View 4 Replies View Related

Truncate Or Rebuild Tables?

Mar 8, 2001

I frequently have tables that I want to empty and refill. I thought I read somewhere that trucating was a bad idea, example:

Truncate table tblFruits

A bad idea because of something to do with the log? I'm a beginner so I don't understand transaction processing very well, or logs.

Is it better to delete the table and rebuild it in code before you add the values?

View 1 Replies View Related

Truncate All Tables In DB With RI Intact

Jun 22, 2004

Anyone?

I tried the following code...thought I could trap the RI Error and move on...

It raises all the way out though....

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'myTable99' because it is being referenced by a FOREIGN KEY constraint.

I thought you could trap it this way...


CREATE TABLE myTable99(Col1 int PRIMARY KEY)
GO
CREATE TABLE myTable00(Col1 int, Col2 int, PRIMARY KEY(Col1,Col2), FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
GO

INSERT INTO myTable99(Col1) SELECT 1
INSERT INTO myTable00(Col1, Col2) SELECT 1,2
GO

CREATE PROC myDynamicSQL99 @sql varchar(8000) AS EXEC(@sql)
GO

DECLARE @error int, @TABLE_NAME sysname, @sql varchar(8000), @x int, @rc int
SELECT @error = 0, @x = 0

DoItAgain:

DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME LIKE 'myTable%'
ORDER BY TABLE_NAME DESC

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'TRUNCATE TABLE ' + @TABLE_NAME
SELECT @SQL
EXEC @rc = myDynamicSQL99 @SQL
SELECT @Error = @@Error
SELECT 'Error Code: ' + CONVERT(varchar(15),@Error) + ' @rc: '+ CONVERT(varchar(15),@rc)
IF @Error <> 0
SELECT @x = @x + 1
FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
END

CLOSE myCursor99
DEALLOCATE myCursor99

IF @x <> 0
BEGIN
SELECT @Error = 0
GOTO DoItAgain
END
GO

DROP PROC myDynamicSQL99
DROP TABLE myTable00
DROP TABLE myTable99
GO

View 14 Replies View Related







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