Dear all,
how can I use a store procedure to get the structure of a table like data type, length, description etc.
I have used sp_columns to get the information but it can't get the description of each field.
Thanks.
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies View RelatedBelow is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.
Structure Number1
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Structure Number2
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Can anyone tell me that between above 2 structure what is better and why?
I just add CategoryNativeId(P.K.) in Number 2 structure.
How can I get the structure of a table?
With MySql I can do it this way: DESC | DESCRIBE <tablename>
Thank you,
Dirk
Dirk.Ulrich@gmx.de
How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials.
2) I need to create hardware using labour and materials.
3) I need to be able to include some hardware in some products.
4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts (
ProductID int NOT NULL,
Product varchar(50)
)
go
ALTER TABLE tbProducts
ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID)
GO
CREATE Table tbMaterials (
MaterialID int NOT NULL,
Material varchar (50)
)
GO
ALTER TABLE tbMaterials
ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID)
GO
CREATE Table tbLabour (
LabourCode char (2) NOT NULL,
Labour varchar(50)
)
GO
ALTER TABLE tbLabour
ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode)
GO
CREATE Table tbProductMaterials (
fkProductID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbProductLabour (
fkProductID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_pk PRIMARY KEY (fkProductID, fkLabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbHardware (
HardwareID int NOT NULL,
Hardware varchar(50)
)
go
ALTER TABLE tbHardware
ADD CONSTRAINT tbHardware_pk PRIMARY KEY (HardwareID)
GO
CREATE Table tbHardwareMaterials (
fkHardwareID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_pk PRIMARY KEY (fkHardwareID, fkMaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_Hardware_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
CREATE TABLE tbHardwareLabour (
fkHardwareID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_pk PRIMARY KEY (fkHardwareID, fkLabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_Product_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
This table structure does not include anything about accounts and item 4) making materials from 1 or more other materials (E.G., Concrete).
Any hints how to incorporate these outstanding items?
Mike B
How can I get my table structure (columns, their types, whether it s an Identity column) using T-SQL
pls
Thanks you
Hi
How to write a query for table structure which looks like
SQL Design table.
thanks
asm
I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)
please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.
I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s)
An example is put all together, (not sure if it is the right and efficient way)
An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data
(table structure) - (example with 3 users)
userId | timestap | message
1 12/12/2007 10:00 some message
1 12/12/2007 11:00 some message
1 12/12/2007 12:00 some message
1 12/12/2007 13:00 some message
1 12/12/2007 14:00 some message
2 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 12:00 some message
3 12/12/2007 13:00 some message
3 12/12/2007 14:00 some message ...etc..
Thank you..
Hi Guys,
I have another question.
Is there a query to display the structure of a certain table?
e.g. display the columns, data type,...etc.
TIA
Hi,I am new to XML and need a structure of table in XML format, which needsto be created as a table in another server.The Source and destinationservers are SQL Server 2000. Could anyone help me on this.Balaji.S.--Posted via http://dbforums.com
View 1 Replies View Related
Hi,
I have one field "Status" with 15 different types of values. Like 1, 2, 3, 4 .. 15.
Now this Status field contains the values like this:
Id Status
1 1,2
2 3
3 1,2,3
4 13,15
What is the best way to organize this information in SQLServer table?
My idea is Create one table with the following volumns: ID, Status1, Status2, Status3, ... Status14, Status15.
Now the above data look like this:
Id Status1 Status2 Status3 ... Status13 Status14 Status15
1 1 1 0 0 0 0
2 0 0 1 0 0 0
3 1 1 1
4 0 0 0 1 0 1
So, In future If they add new column they can easily add column like status16.
And status table look like this
Status
StatusID Description
1 a
2 b
..
15
Thanks in advance
All,On my aspx page i have 2 sets of checkboxlists, each with a number of items and i am storing that info in my db. currently i have my table set up this way: InfoID--PK autoUserID-intCheckBoxList1ID-intCheckBoxList2ID-int So a user can select more than 1 item in each checkbox, so this table will contain multiple user-ids and each id from the respective checkboxes. My question is, is there a better way to store this data? I hope this is clear, any input would be greatly appreciated.Thank you.
View 2 Replies View RelatedHi....I execute select statement on three tables and i get the following table:
ID
Value
Name
1
10
color
2
20
color
3
30
color
4
40
color
Hi,I have a table for storing my picture albums here it is:CREATE TABLE [UserAlbum] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [UserID] [int] default(0) , [AlbumName] [nvarchar] (100) NULL , [Audience] [int] default(0) , [Date_added] [smalldatetime] NULL , CONSTRAINT PK_CmmNet_userAlbum_ID PRIMARY KEY CLUSTERED ( ID )) ON [PRIMARY]GO
it has a field called "audience" as the name defines i want to choose who will see my albumsit is integer and as follows:0 value=everyone1=friends only2=private
as i started to write a query for it i came into this problem: for exampleif i write SELECT * FROM UserAlbum where audience=1 then only friends can see the infowhat happens to everyone with value of 0?when i set it to 0, friends should also be able to see the info because it is set for everybody
i noticed i should use "in cluase" like thisSELECT * FROM UserAlbum where audience in ('0','1') > so in this way both everyone and also friends can see the info
or maybe i should add 3 different fields for private,public,friends
it seems to be simple but at the same time harddoes anyone have a suggestion?
Hi i have a student table and also an societies table, and a student can belong to as many societies as possible, for example student John Taylor belongs to the Football society, rugby societey and dance society, where as student Jim Jenkins belongs to the Football society, rugby society, arts society and graphics society.
My tables so far look like this;
STUDENTS; student_id, student_name
SOCIETIES; society_id, society_name
My question is how can i make the scenario above reflect in my table structure, would i need to create another table, if so what fields or columns should i place in this new table, thank you.
I'm a VB.net programmer working in an environment that doesn't have a true sql db administrator. We have a situation where we're thinking about adding a new database (db2) with the same Tables used in an existing database (db1). We were hoping to avoid this, but circumstances are forcing us to think about it. Are there any sql tools out there to maintain a dual-db like this? What are the main disadvantages to this?
I just started with SQL 7 and I would like to know how to print
out the structure of a table in SQL 7.
thanks in advance.
This has to be a very simple think to do, but, I`m new....
Have NT4.0, SQL6.5, and existing database with about 30-40 tables.
Just want to be able to print out each table structure with record info. Don`t need the actual data printed, just the table/record structure.
Thanks, and again, I`m new to SQL/Query so go easy on me.... ;-)
Ron
Hello
I have replicated 1 database.Now i need to change the datatype length of one feild from varchar 12 to varchar 20.But when i try to do that it shows error ODBC error !Cannot modify database because it is in replication mode.
Can any body help me to sort out this problem.
Thx
Bilal
I am using Microsoft SQL Server Management Studio Express to connect my MS SQL database.
I have 2 databases, db1 and db2.
recently i have created third database, that is db3.
i wish to have db3 to have the same table structure as db1. how can i export and insert into db3? i only need all tables structure, but not the data.
Thanks.
hi
how can i copy a table structure from a database to another(without bakup)?
Hi,
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Greg
Hi,
In SQL Server, Is there any DDL available to copy a table structure alone and not the data,
I believe 'SELECT * into new_table from table1' will copy both structure and data as well.
Please advice,
Thanks,
MiraJ
I need some help with the following scenario. I am creating an app that allows users to take a survey with questions that change dynamically based on the answers that are given to previous questions. I currently have the tables structured as follows:
Table 1 - Questions
QuestionID (Int), Question (NvarChar)
Table 2 - Completed Survey
SurveyID (Int), Question 1(Int), Question 2 (Bit), Question 3(Text)
I don't know of a way to relate Table 2 to Table 1 as shown in the example. This leads to a lot of unneccesary coding on the other side when trying to read data back out of the tables for reporting purposes.
I know I could always switch Table 2 to the following structure:
SurveyID (Int), QuestionID (Int), Answer(???)
But given the fact that the answers can be many datatype's I'm not sure it's the best way to go. Theoretically I'd have to either do a lot of type conversion to text and back, or union multiple tables, 1 for each different datatype.
Does anybody have any suggestions that might make my life easier. Am I missing something simple here???
Hi all,
How can i get the information as to when was the last my particular table structure was changed (for ex. adding of a column/dropping a column)
Pls let me know
TIA
Hi all
I am developing a site for a client but unfortunately
I havn't much of a clue regarding db/table design.
Scenario:
The client intends to sell window blinds over the internet.
The site has been developed using Asp.Net2 and C# with Server 2005.
I need to develop a price lookup control that has access to 4 parameters:
ManufacturerID (from existing table Products.manufacturerID)
PriceBandID (from existing table Products.PriceBandID)
Width (input by the user)
Drop (input by the user)
The idea being upon button click, the correct price is returned based on those parameters.
The price structure has been given to me in the form of Excel files.
There are around 20 different manufacturers
each with a 7 band (A - G) price matrix.
My question is what would be the most efficient way to represtent
this pricing structure under the current environment?
Will I really need 20 * 7 tables?
Any help, info, advice, pointers, links or comments would be most welcome!
Thanks for your time.
Hi
Can i able to see the all table structure at once throught the Select
statement.
How?
thansk
asm
Hi All
I want to convert table structure into xml in the following format.
Eg: <TABLE_NAME>
<COLUMN_NAME>
<IS_NULLABLE> </IS_NULLABLE>
<CONSTRAINT_TYPE> </CONSTRAINT_TYPE>
<CONSTRAINT_NAME> </CONSTRAINT_NAME>
<TABLE_CATALOG> </TABLE_CATALOG>
<LENGTH> </LENGTH>
</COLUMN_NAME>
</TABLE_NAME>
where TABLE_NAME is the table name in database and COLUMN_NAME is name of the column in above table and the remaning are the attributes to that column.
please suggesst me the sql query to get the following structre of any table in database,
Thanks
Rupal
Help :-) I can't see how this should be laid out!Consider the scenario of a car rental company. They have "cars" and "lots"(nationwide) and "customers." So far it is easy to query each of these. Acar is rented by a customer so there would be say a "rentals" table. A caris rented and returned over and over so there would be many rentals for eachcar. One thing to remember is that the car isn't assigned to a specific lotso either the customer has the car or it is in a lot (somewhere) andavailable for rental.So the question is how do I layout the tables (and query) to find out wherea specific car is? Or where all the cars are for that matter. I don'tthink it is as simple as it looks at first glance. Clearly there can be acolumn in the car table containing a LocationId but that wouldn't indicatewhether it is a LotId or a CustomerId. There could be a column for bothLotId and CustomerId and with only one filled in at a time but that doesn'tlend itself to an elegant SQL query does it?I thought there might be a LocationIdType field. It could be set toindicate whether the LocationId is a "lot" or a "customer" which permits meto join with the lots or customers table but I wondered if there is anotherway.Is that enough information to go on? I can give more details if it helps.Thanks,Tom
View 25 Replies View RelatedHello to everyone!How can I print the structure of a table from the SQL SERVER ENTERPRISEMANAGER?Thank YouFabio*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View Related