Microsoft SQL Server
Скрипты
T-SQL

функция, возвращающая поля, идентифицирующие рекодсет

Опубликовано: 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'

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Скрипты / T-SQL / функция, возвращающая поля, идентифицирующие рекодсет