sp_MS_Marksystemobject

exec sp_MS_Marksystemobject sp_name

Allows you to mark your own stored procedure 'sp_name' as a system object.

(It cannot then be accidently deleted)

Filegroups : Where are my Indexes / Tables Stored?

Which filegroups are utilised by which database objects?
A Tsql script to help out...

SELECT
   fg.data_space_id
 , fg.name
 , OBJECT_NAME(p.object_id) AS [ObjectName]
 , p.index_id
    , dbf.name
    , dbf.physical_name
    , dbf.size*8/1024 AS [Size] -- Size of file 
    -- size is in number of 8KB pages. Multiply by 8 to get figure per extent (64KB),
    -- then divide by 1024 to get index size in MB.
 FROM sys.filegroups fg
 LEFT JOIN sys.database_files dbf
      ON fg.data_space_id = dbf.data_space_id
 LEFT JOIN sys.partitions p
      ON fg.data_space_id = p.partition_number
 WHERE (p.object_id>4096 OR p.object_id IS NULL)

Examining SQL Column types (2005+)

select o.type_desc, o.name, c.name, t.name , c.max_length, c.precision
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.objects o
on  c.object_id = o.object_id
where o.type_desc not like 'system%' 
and o.type_desc not like 'int%'
order by 1,2,3,4

TSQL : List tables, Indexes & Columns

List tables, Indexes & Columns via system objects -

select sys.objects.object_id,
sys.objects.name as tablename,
sys.indexes.name as indexname,
sys.columns.name as columnname
from sys.objects
inner join sys.indexes 
on sys.objects.object_id = sys.indexes.object_id
inner join sys.index_columns 
on sys.index_columns.object_id = sys.indexes.object_id
and sys.index_columns.index_id = sys.indexes.index_id
inner join sys.columns
on sys.columns.object_id = sys.index_columns.object_id
and sys.columns.column_id = sys.index_columns.column_id
where sys.objects.type_desc  = 'USER_TABLE'
and sys.indexes.is_primary_key = 0

TSQL : Users & Logins

TSQL for querying systems objects for database users & logins (2000 & 2005) -

--sql 2000 -- list database users
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) -- exclude internal sql acounts

--sql 2005 -- list database users
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts

-- sql 2000 -- list orphanned users for current database
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 -- list orphanned users for current database
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

-- code to demonstrate sql users present in db, but not mapping to sql server logins

select * from sys.sysusers
      join sys.syslogins
  on sys.sysusers.name = sys.syslogins.name
    and sys.sysusers.sid <> sys.syslogins.sid

--sql 2000 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sysusers where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

Find all internal sql objects (including undocumented ones)

Note is_ms_shipped clause below -

SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND [is_ms_shipped] = 1
ORDER BY [name];

To find functions, use type of  -
FN SQL Scalar Function
IF Inline Table Valued Function
TF SQL Table Valued Function

To find views, use type of  -
V View

To find procedures, use type of -
P SQL Stored Procedure
PC CLR Stored Procedure
X Extended Stored Procedure

Counts of database objects

-- sql 2005 - database object counts

if object_id('tempdb..#objecttypes') is not null
begin
drop table #objecttypes
end
create table #objecttypes (otype char(5)collate Latin1_General_CI_AS ,typedesc varchar(50),primary key clustered(otype))

insert into #objecttypes(otype,typedesc) values('AF','Aggregate function (CLR)')
insert into #objecttypes(otype,typedesc) values('C','Check constraint')
insert into #objecttypes(otype,typedesc) values('D','Default (constraint or stand-alone)')
insert into #objecttypes(otype,typedesc) values('F','Foreign Key constraint')
insert into #objecttypes(otype,typedesc) values('PK','Primary Key constraint')
insert into #objecttypes(otype,typedesc) values('P','SQL Stored procedure')
insert into #objecttypes(otype,typedesc) values('PC','Assembly (CLR) stored procedure')
insert into #objecttypes(otype,typedesc) values('FN','SQL scalar function')
insert into #objecttypes(otype,typedesc) values('FS','Assembly (CLR) scalar function')
insert into #objecttypes(otype,typedesc) values('FT','Assembly (CLR) table-valued function')
insert into #objecttypes(otype,typedesc) values('R','Rule (old-style, stand-alone)')
insert into #objecttypes(otype,typedesc) values('RF','Replication-filter-procedure')
insert into #objecttypes(otype,typedesc) values('S','System base table')
insert into #objecttypes(otype,typedesc) values('SN','Synonym')
insert into #objecttypes(otype,typedesc) values('SQ','Service queue')
insert into #objecttypes(otype,typedesc) values('TA','Assembly (CLR) DML trigger')
insert into #objecttypes(otype,typedesc) values('TR','SQL DML trigger')
insert into #objecttypes(otype,typedesc) values('IF','SQL inline table-valued function')
insert into #objecttypes(otype,typedesc) values('TF','SQL table-valued-function')
insert into #objecttypes(otype,typedesc) values('U','Table (user-defined)')
insert into #objecttypes(otype,typedesc) values('UQ','Unique constraint')
insert into #objecttypes(otype,typedesc) values('V','View')
insert into #objecttypes(otype,typedesc) values('X','Extended stored procedure')
insert into #objecttypes(otype,typedesc) values('IT','Internal table')

select otype,typedesc,count(s.type) as total
from #objecttypes
left join sys.objects s
on #objecttypes.otype collate Latin1_General_CI_AS = s.type collate Latin1_General_CI_AS
group by otype, typedesc

SQL 2000 : Table Indexes via System Objects

SELECT  SysUsers.[Name] AS Owner, Object_Name(SysIndexes.ID)  AS TableName, SysIndexes.[Name] AS IndexName, [InDID] 
From SysIndexes
Inner Join SysObjects On SysObjects.[ID]=SysIndexes.[ID]
Inner Join SysUsers On SysUsers.[UID]=SysObjects.[UID]
Where ObjectProperty(SysIndexes.[ID],'IsSystemTable')=0  
And ObjectProperty(SysIndexes.[ID],'IsUserTable')=1 
And [InDID]>0 
And [InDID]<255 
And [first] IS NOT NULL