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