Re: Triggers und History

Hallo Albert,

noch was ist mir aufgefallen:


-- get primary key column name
select @pkCol = [name]
from syscolumns
where [id] in (
select [id]
from sysobjects
where [name] = @tbl
)
and colid in (
select SIK.colid
from sysindexkeys SIK
join sysobjects SO on SIK.[id] = SO.[id]
where SIK.indid = 1
and SO.[name] = @tbl

Der Index mit ID 1 ist NICHT ZWANGSLÄUFIG der Primary Key. Es ist der
Clustered Index.

So wirst Du bei dieser Tabelle:

CREATE TABLE Heap (
ID PRIMARY KEY NOT CLUSTERED,
F1 int
)

gar nichts zurückbekommen, und bei dieser Tabelle:

CREATE TABLE CIWithOtherPK (
ID PRIMARY KEY NOT CLUSTERED,
F1 int
)

CREATE INDEX CI ON CIWithOtherPK(F1)

ein falsches Feld (F1 anstelle von ID)


Primary Keys sind nämlich primär keine Indexes, sonderen Constraints (also
in sys.objects): SELECT * FROM sys.objects WHERE type='PK', wobei Du auf
"parent_object_id" joinen musst



GP


.

Relevant Pages

  • Re: Update Identity Column
    ... (x INT NOT NULL PRIMARY KEY CLUSTERED, ... INSERT INTO tbl VALUES; ... David Portas, SQL Server MVP ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Designing Block
    ... Vehicle RegNo tbl: ... RegNo - primary Key ... Make/ModelID - primary key ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Split table containing data..possible in this case?
    ... CONTACT_ID (Primary Key - Autonumber) ... link your spreadsheet as a table. ... Create an append query that will append records to the GroupCode table. ... Opened tbl C and given a primary key to ID ...
    (microsoft.public.access.gettingstarted)
  • RE: Split table containing data..possible in this case?
    ... IDC has a Primary Key IDC no Primary Key one-to-many ... spreadsheet and your two tables, ... Opened tbl C and given a primary key to ID ... I would suggest one append query to pull the columns ...
    (microsoft.public.access.gettingstarted)
  • RE: Split table containing data..possible in this case?
    ... CONTACT_ID (Primary Key - Autonumber) ... link your spreadsheet as a table. ... Create an append query that will append records to the GroupCode table. ... Opened tbl C and given a primary key to ID ...
    (microsoft.public.access.gettingstarted)