I wanted to find all occurrences of ADRSCODE in a Database where ADRSCODE is in either an Index or a Primary Key.
I know how to get all of the occurences of ADRSCODE in a database and the table associated with it, I just want to tack on the Index and/or primary key.
SELECTOBJECT_NAME(object_id)FROMsys.columns
WHEREname
='foo'
How can I get the other bit of information ?
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?
I have a 3rd party app which had a primary key with about 5 fields. The last field of this was a trantype. This app had a posting process which uses this in it's sql. Ran rather slow. We added an individual index to this field and cut processing down 90%. It almost seemded like sl server was ignoring this index. Is this because it was the last field in the primary key index?
View 1 Replies View RelatedAll of the 3 books I've read say it is not a good idea to create a clustered index on the primary key but it is created as the default. My question is has this changed in 2005? My understanding is to create the clustered index on columns used first in join clauses and then in where clauses, what is the answer?
View 14 Replies View RelatedI have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed
Thank you
What query do I use to list the primary key for each user table, i.e.TABLE | PRIMARY_KEY |Regards,Alan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies View RelatedThis ain't working
SELECT T.TABLE_NAME,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),
'TableHasIdentity') = 0
AND T.TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(C.COLUMN_NAME),'IsPrimary Key') = 1
ORDER BY T.TABLE_NAME,C.COLUMN_NAME
This is giving me bogus results...
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID(COLUMN_NAME),'IsPrimaryKe y') = 1
I have PK's all over the place. What gives? Too many cocktails with lunch?
I wrote a query that when run, will give me a listing of all tables in a database, and whether or not those table have primary keys.
Here's the query:
Code Snippet
select so.name, si.name from sysobjects so
left outer JOIN sysindexes si on so.id = si.id
where si.status = '2066' and so.type = 'u'
order by so.name asc
It works for the most part save for one small (big) problem. It gives me a list of tables that have a corresponding primary key, but doesn't list those without one. For example, in one database I run this query. I know for a fact that there are 26 tables in this database, and my query returns 16 rows (one for each table with a PK). I'd like to see those as well as those without one, with a NULL in the NAME column for the PK.
I've tried every join combo under the sun and still can't get the desired results.
Any insight would be appreciated!
Thanks!
Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
...got tired of looking at them by hand.
Cheers
-b
DECLARE @vcDB varchar(20),@vcSchema varchar(20),@vcTable varchar(200)
Select @vcDB='mydb',@vcSchema='dbo'
DECLARE cLoop cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=@vcDB
and TABLE_SCHEMA=@vcSchema
order by TABLE_NAME ASC
open cLoop
FETCH NEXT FROM cLoop INTO @vcTable
WHILE @@FETCH_STATUS=0
BEGIN
if not exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = @vcSchema
AND TABLE_NAME = @vcTable
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print @vcTable + ' does not have a primary key'
FETCH NEXT FROM cLoop INTO @vcTable
END
Close cLoop
DEALLOCATE cLoop
Table 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?
CREATE TABLE StockNames
(
-- Added Primary key to [stock_symbol]
[stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY,
[stock_name] VARCHAR(150) NOT NULL,
[stock_exchange] VARCHAR(50) NOT NULL,
[code].....
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
I have imported a whole bunch of tables. Most of them have an ID (int) column. Is there a way to set the ID columns across all tables to auto increment Primary Keys in bulk?
View 11 Replies View RelatedHello,
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
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.
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
How do i find all the tables that have an Identity Column as a primary key in a database. Thanks.
View 8 Replies View RelatedCan somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
I have a database in which I have some tables in which I have implemented Clustered columnstore Index. How to find the fragmentation levels of all these indexes via a single T-SQl script
View 3 Replies View RelatedI have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.
I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used.
I have table with some columns with no primary key..
I have to find the combination of columns which may the row unique...
How to do that like microsoft...
i have some confusions with keys and indexes.. plz let me know whether the following are correct..- Every Primary Key is a Clustered Index- A Primary Key cannot exist without a Clustered Index- Every Unique Key is a Non-Clustered Index - Non-Clustered Index is the DEFAULT Index- A table can have only 1 Primary key- A table can have only 1 Clustered Index- A table can have any number of Unique Keys- A table can have any number of Non-Clustered Indexes
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A
LEFT JOIN #SqlStatement B ON A.spid = B.spid
where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C
ON T.object_id=C.object_id
WHERE T.type='U'
)
Something like this : find the column names and tables name
UPDATE P
SET
P.IsError=1
,P.IsDrawingRevNo=1
,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)'
FROM ZPTSMGR.ProjectDrawingRaw P
WHERE P.LogId=@LogId AND P.ProjectId=@ProjectId AND P.Revision > 32767 (P.Revision NOT BETWEEN -32768 AND 32767) --SMALLINT RANGE -32768 to 32767.
--DataType Range
--tinyint DataType (MinVal: 0, MaxVal: 255). Its storage size is 1 byte.
--smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes.
--int DataType -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes.
--Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.
The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value
Hello,
I am new in SQL Server, I have to deal with this big database with many tables, is there any way I can get a list of all primary keys , foreign keys and indexes and on all tables?
Thanks,
I'm trying to drop all indexes and primary keys so that i can rebuild them(from a script created from same database on another server).when i go to the 'generate sql scripts', it has the ability to drop orgenerate alltables. it also has the ability to generate all keys only. but i cant finda wayto drop all of these keys...any ideas?tiawoody rao
View 1 Replies View RelatedI am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property
thanx
If i create a simple table with a foreign key constraint, does itcreate an implicit index on that given ID? I've been told this isdone in some databases, but i need to know for sure if SQL Server doesit. Has anyone heard of this before, on any other databses perhaps?Heres an example of how the foreign key constraint is being added:ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])My initial testing seems to indicate adding an index on the foreignkey column helps, but i need to know for sure. Any insight would begreatly appreciated!Bob
View 6 Replies View RelatedFor our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."
can soemone explain to me why i have this kind of error?
i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!
all the values from of application_input will be stored also in approve_overtime.
sometimes the datas can be stored.sometimes it cannot and produces an error!
what do u think?
hmmm pls help!