create procedure sys.sp_MSreplsup_table_has_pk
(
@tabid INT
)
as
begin
set nocount on
-- if it's a table, check that it has a PK
-- if it's a view, see if it has an index ( MVs must have a unique CI )
IF EXISTS (SELECT so1.name
FROM sys.objects as so1
join sys.objects as so2
on so2.object_id = so1.parent_object_id
WHERE so1.parent_object_id = @tabid
AND so1.type = 'PK'
AND so2.type = 'U')
BEGIN
RETURN 1
END
IF EXISTS (SELECT *
from sys.objects as so
join dbo.sysindexes as si
on si.id = so.object_id
WHERE so.object_id = @tabid
AND so.type = 'V')
BEGIN
-- evaluate keys, make sure none are nullable
DECLARE @indkey int
,@objname nvarchar(256)
SELECT @indkey = 1
select @objname = QUOTENAME(schema_name(OBJECTPROPERTY(@tabid, N'SchemaId'))) + N'.' + QUOTENAME(object_name( @tabid ))
WHILE @indkey <= 16 and index_col( @objname, 1, @indkey ) is not null
BEGIN
IF NOT EXISTS( SELECT * FROM sys.columns
WHERE object_id = @tabid
AND name = index_col( @objname, 1, @indkey )
AND is_nullable = 0 )
BEGIN
RETURN 0
END
SELECT @indkey = @indkey + 1
END
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
end