MyTableType Table Type
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'MyTableType' AND ss.name = N'dbo')
DROP TYPE [dbo].[MyTableType]
GO
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[Col01] [int] NULL,
[Col02] [varchar](30) NULL,
[Col03] [varchar](30) NULL
)
GO
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'MyTableType' AND ss.name = N'dbo')
PRINT '<<< CREATED TYPE dbo.MyTableType >>>'
ELSE
PRINT '<<< FAILED CREATING TYPE dbo.MyTableType >>>'
go
GRANT REFERENCES, EXECUTE ON TYPE::dbo.MyTableType TO MyRole
go
List all permissions for all table types.
SELECT
[schema] = s.name,
[type] = t.name,
[user] = u.name,
p.permission_name,
p.state_desc
FROM sys.database_permissions AS p
INNER JOIN sys.database_principals AS u
ON p.grantee_principal_id = u.principal_id
INNER JOIN sys.types AS t
ON p.major_id = t.user_type_id--.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE p.class = 6; -- TYPE
Comments