ronmichael

/*
   if you have a table of rows with parent rows, this will return one row for each row and each of its parents.
   thanks to http://stackoverflow.com/questions/13487006/use-sql-server-cte-to-return-all-parent-records.
*/

with items(uniqueid,parentid) as (

	select uniqueid, uniqueid
	from equipment

ronmichael

select type_desc, name
from sys.objects o
left join sys.database_permissions p on p.major_id=o.object_id
where p.class is null
and type_desc not in ('SYSTEM_TABLE','INTERNAL_TABLE','SERVICE_QUEUE','SQL_TRIGGER','TYPE_TABLE')
and type_desc not like '%CONSTRAINT%'
order by type_desc, name

ronmichael

select
	dbo.sysobjects.name TableName, 
	dbo.syscolumns.name ColumnName, 
	dbo.systypes.name as Type
from dbo.sysobjects 
join dbo.syscolumns on dbo.syscolumns.id = dbo.sysobjects.id 
join dbo.systypes on dbo.systypes.xtype = dbo.syscolumns.xtype
where dbo.systypes.name != 'sysname'