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
Last modified: August 20, 2020

Author

Comments

Write a Reply or Comment