Опубликовано: 07 июн 05
Рейтинг:
Рейтинг:
Автор: Lepsik
Прислал: Lepsik
---------- function return list of fields identifying recordset ----------------------
USE Northwind
if object_id('dbo.fn_identity_fld') is not null drop FUNCTION dbo.fn_identity_fld
GO
CREATE FUNCTION dbo.fn_identity_fld( @nametbl nvarchar(256) )
RETURNS nvarchar(2000)
AS
BEGIN
DECLARE @fld nvarchar(2000)
declare @objid int
set @objid = object_id(@nametbl)
-- check if table is exising
IF @objid IS NOT NULL
BEGIN -- try to read identity field
select @fld = a.name from syscolumns a, systypes b, sysobjects c
where a.id=c.id and c.type='U' and a.xusertype=b.xusertype and lower(c.name) = lower(@nametbl)
and (a.status & 0x80 =0x80)
IF @fld IS NULL
BEGIN
-- try to find minimum set of fields
DECLARE @U int, @P int
SELECT @p=(SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE CONSTRAINT_TYPE='PRIMARY KEY' and cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and cu.TABLE_NAME = @nametbl),
@u = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE CONSTRAINT_TYPE='UNIQUE' and cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and cu.TABLE_NAME = @nametbl)
DECLARE @TYPEFIELD nvarchar(24)
SET @TYPEFIELD = 'PRIMARY KEY'
IF @U < @P
SET @TYPEFIELD = 'UNIQUE'
-- try to read requred fields
set @fld = N''
SELECT @fld = @fld + N',' + cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE CONSTRAINT_TYPE=@TYPEFIELD and cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and cu.TABLE_NAME = @nametbl
ORDER BY ORDINAL_POSITION
SET @fld = SUBSTRING(@fld, 2, LEN(@fld)-1) -- cut off first symbol
END
END
RETURN @fld
END
GO
if object_id('dbo.a1') is not null drop TABLE dbo.a1
CREATE TABLE dbo.a1 (id int not null identity, name nvarchar(20) unique, primary key(id, name))
GO
if object_id('dbo.a2') is not null drop TABLE dbo.a2
CREATE TABLE dbo.a2 (id int, name nvarchar(20) unique, primary key(id, name))
GO
if object_id('dbo.a3') is not null drop TABLE dbo.a3
CREATE TABLE dbo.a3 (id int, name nvarchar(20) primary key, unique(id, name))
GO
select dbo.fn_identity_fld( N'a1' ), N' -- identity keys'
select dbo.fn_identity_fld( N'a2'), N' -- unique field'
select dbo.fn_identity_fld( N'a3'), N' -- primary field'
Комментарии