How To Retrieve Primary Key Columns From Db?
Jan 12, 2007
Hello all. I need to extract the column names that form the primary key group on a table in SQL Server. I have a table called Account and it contains ten columns. The primary key consists of two columns - MasterAccountID, AccountID. This primary key is a unique constraint and is clustered (it acts as an index as well as a primary key group). I have tried the following to no avail:
exec sp_pkeys Account -> returns no rows
exec sp_helpindex Account -> throws an error stating that the object 'Account' does not exist
If I run the following SQL statement, I can see all of the PK_* constraints in the database, so I know they are there:
select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')
Again, I need to be able to specify a table name and have it return the columns (don't care if it returns extra fields) that make up the primary key fields for that table. Thanks!
View 2 Replies
ADVERTISEMENT
Nov 21, 2006
Hello!
I want to write a stored procedure, that will insert new row into a table and return the key of newly inserted row.
View 2 Replies
View Related
Sep 29, 2006
Visual Basic 2005 Express:
I want to retrieve an SQL DataBase table record whose primary key is 4.
How do I read in that record and how do I pick up data from it?
View 6 Replies
View Related
May 3, 2007
Hi everyone,
On daily basis I need to generate excerpts by mean of Excel. Prior to sql25k I used to play with Enterprise Manager and pick up the name of all the columns for a table very easily doing this
select top 1 f1,f2,-.. from table
name age
enric 80
How do I such thing from Sql Management Studio??
It's a silly thing, I know, but it's very useful for me because when I've got those columns then I can do paste them perfectly into .XLS.
Otherwise I see forced to write one by one and sometimes tables have more than 60 columns
It's not useful generate a CREATE TABLE script or launch SP_HELP <MYTABLE> because I obtain the name of the columns in vertical no horizontal.
Thanks a lot!!!
View 8 Replies
View Related
Sep 25, 2015
i have this schema :
CREATE DATABASE ANDEB
USE ANDEB
CREATE TABLE TDocHeader
(
CustName VARCHAR(50) NOT NULL,
DocNum INT NOT NULL,
col1 varchar(50),
[code]...
How i can have a group by for last docnum for Customer and all columns?
View 6 Replies
View Related
Apr 27, 2008
hi
i want to retrieve all columns for all tables (for all databases) by this system sp :
Code Snippetexec sp_msforeachtable 'exec sp_columns ''?''' but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.
View 8 Replies
View Related
May 16, 2008
I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this:
1. SELECT name FROM table1
2. SELECT name FROM table2
3. Join the two results together and order them alphabetically
4. Return the result set
I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.
View 6 Replies
View Related
Oct 31, 2006
Hi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advance
View 6 Replies
View Related
Apr 6, 2008
Hi guys,
Working on a MS Access database, I have a table named "myTable" which contains several fields.
I just want to retrieve all the fields (columns) in the myTable, without retrieving Col1 and Col2
What should my SQL string be?
SELECT * (not Col1, Col2) FROM myTable
Thanks in advance for any help.
Aldo.
View 5 Replies
View Related
Jan 30, 2008
Hi,
SQL Server 2005 has a new very useful feature for creating non-clustered indexes called INCLUDE <columns> which are very helpful when trying to create covering indexes.
Does anyone know of a way to retrieve these INCLUDE columns through any of the system metadata tables? The sp_helpIndex stored procedure is what I currently use but that only returns the (sorted) index columns and not the include columns.
Thanks in advance,
Gordon Radley
View 1 Replies
View Related
Jan 19, 2012
I am using Query Writer (should be SQL 2005) and have included the following code.
The end result: -should retrieve columns and display the count for two different data sources that were added by personnel in a specific department.
Problem: results are returned but not accurate. The code below works just fine and returns the results for all spot buy orders added by personnel in the sales department. However, I want to add a column in the same report that also counts blanket orders from the exact same table added by personnel in the sales department. The database uses views (v) in lieu of dbo.
Parameters:
@Add_Date_From SMALLDATETIME='',
@Add_Date_To SMALLDATETIME='',
@Last_name VARCHAR(50)='',
@First_Name VARCHAR(50)=''
Query:
SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.PO_Type) AS 'Spot Buy Added'
[Code] ....
If I substitute COUNT(T4.PO_Type) AS 'Spot Buy Added' with COUNT(T4.PO_Type) AS 'Blanket Added' I also get accurate results for the blanket order. IE separately they work just fine. If I try to combine the two that is where the trouble begins.
What am I doing incorrectly when I try to add the criteria/code for the additional column to count the blanket orders?
View 9 Replies
View Related
Dec 27, 2014
I need to retrieve columns names(instead of select * from) as single row from table in dynamic way.
i m using following query.
its working fine while using from selected database. but its not working when i m running from different database.
DECLARE @columnnames varchar(max)
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
I need to pass database name dynamically like using by variable.
Is this possible to use variable after from clause. or any other methods?
eg:
DECLARE @columnnames varchar(max)
DECLARE @variable='db_month11_2014'
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from @VARIABLE.INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames
View 9 Replies
View Related
Sep 10, 2015
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
View 3 Replies
View Related
Dec 7, 2001
Hi Guys,
When I create a table with a composit primary key with 18columns. I get this error
"Cannot specify more than 16 column names for index key list. 17 specified."
I really have to have these columns as primary key.
Is there any workaround to create this table;
Thanks
View 3 Replies
View Related
Apr 15, 2007
How can I create a primary key on two columns? I could not find any sensible code sample or description how it should be done. It is clear from MSDN that it is possible but there are no examples.
There is a textbox "Included Columns" in one of the tools for setting primary keys for one column but it does not allow me to enter anything.
Thanks.
View 3 Replies
View Related
Jan 5, 2006
I want to get the Primary Key Columns in Arrays by sending a tablename. I am using SQL Server 2000 and I want to make a find utility in VB.net whichwill work for all the forms; I have tables with one Primary key and some tables with composite Primary keys. I used to do this in VB 6 by making a function which fills the Primary Keys inList Box (I require to fill in list box), now I need to get in array. Can some one tell me the migration of the following VB 6 Code? This was written for the MS Access, I need same for SQL Server, I cannot find Table Def and Index Object in VB.net 2003. Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName AsString) Dim lttabDef As TableDef Dim liCounter As Integer Dim liLoop As Integer Dim idxLoop As Index Dim fldLoop As Field
With ldbDatabase For Each lttabDef In .TableDefs If lttabDef.Name = lsTableName Then liCounter = lttabDef.Fields.Count For liLoop = 0 To liCounter - 1 cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name Next liLoop For Each idxLoop In lttabDef.Indexes With idxLoop lblIndexName = .Name If .Primary Then liCounter = 0 For Each fldLoop In .Fields cboPrimaryKeys.List(liCounter) = fldLoop.Name liCounter = liCounter + 1 Next fldLoop End If End With Next cboFieldLists.ListIndex = 0 If cboPrimaryKeys.ListCount > 0 Then cboPrimaryKeys.ListIndex = 0 End If Exit For End If Next End WithEnd Sub
View 2 Replies
View Related
Oct 17, 2005
I am currently undertaking a review of the primary keys in a SQL Server2000 database with a view to improving performance of queries.I have heard that, in the case of compound primary keys, it isimportant to select the correct order for the columns within the key.For instance, imagine a table called OrderLine which has primary keycolumns as follows-LedgerOrderNumberOrderLineNumberThe theory I have heard is that columns with the most distinct valuesshould come first. In this case, Ledger is likely to have a maximum of6 distinct values, OrderNumber a maximum of 10 million and OrderLine upto 99. Based on this supposition I believe the best order would be-OrderNumberOrderLineNumberLedgerI have performed a few rudimentary tests that appear to bear this out.I was wondering if anyone else has tried something similar and if sowhat was the result?Thanks,Ross
View 14 Replies
View Related
Feb 23, 2006
my table :
CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]
i must keep ID columns as primary key
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
but now A1+A2 must be unique
how can i do it ?
thank you
View 5 Replies
View Related
Jun 12, 2006
Hi everyone.
I have two tables: the catalog table and the detail table.
The two tables are joined by a two-columns key.
I want to make a Delete sentence for delete all the rows in the Catalog table that aren't in the detail table, in SQL Server.
The only problem is that the key is composed by two colums.
If the Key was maded of one column, that would be easy, like this:
DELETE FROM CATALOG
WHERE CATALOGKEY NOT IN (SELECT CATALOGKEY FROM DETAIL)
But it is possible to make a delete sentence if the key has two columns ?
Thanks
View 2 Replies
View Related
May 29, 2007
Hi ,
Plz help me.
The scenario is as follows
I have a db on sql server 2005.I have replicated the db on sql server 2000.All the data counts seems to be correct.But the online websites are not able to execute queries generating error(Microsoft OLE DB Provider for sql server error '80040e21'.Multiple step OLE DB operation generated errors.Check each OLE DB status value,if available.No work was done.)Same web aspcode is working for other db (which were replicated from sql server 2000).
Also one more thing that was observed is as follows:
1)Prmary key was replicated for some of the tables.When primary keys were removed manually report executed fine.
Thanks in advance
Satish
View 2 Replies
View Related
May 23, 2015
Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.
View 9 Replies
View Related
Sep 24, 2014
I've two tables A, B. In A table, I need to define the primary key with combination of 2 columns and this Primary Key will be a foreign key in table B. Based on these PK and FK I'll be writing a join to get the second column in table B.
View 0 Replies
View Related
Apr 17, 2014
how to find all primary key columns & foreign key columns in all database tables?
View 1 Replies
View Related
Jul 23, 2005
How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you
View 1 Replies
View Related
Jul 7, 2006
I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?
View 2 Replies
View Related
Aug 17, 2006
Dear all,
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
Thanks in advance for any advice.
View 6 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Feb 12, 2007
mahesh writes "HI,
I am new to sql server.
can anybody help me
I have a table named tblqualificationmaster.
can i know the foreignkeys and the table related to this
tblqualificationmaster having foeign keys using stored procedure."
View 1 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Jan 11, 2007
Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
Thanks,
Uma"
View 1 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
Feb 4, 2015
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies
View Related
