T-SQL - Find which Object resides in which FileGroup

To Find which object resides in which FileGroup in a database, run the below queries.

/**************** For SQL 2000 ****************/

Select Distinct OBJECT_NAME(SI.id) AS ObjectName

        ,SO.type AS ObjectType

        ,FG.GroupName AS FileGroupName

from sysindexes SI , sysfilegroups FG ,sysobjects SO

where SI.groupid = FG.groupid and

      SI.id = SO.id

Order by ObjectName

/**************** For SQL 2005 and SQL 2008 ****************/

Select Distinct OBJECT_NAME(SI.object_id) AS ObjectName

        ,OBJECTPROPERTYEX(SI.object_id,'BaseType') AS ObjectType

        ,FG.Name AS FileGroupName

from sys.indexes SI , sys.filegroups FG

where SI.data_space_id = FG.data_space_id

Order by ObjectName

Statistics Updated Date

Statistics in SQL Server refers specifically to information that the server collects about the distribution of data in columns and indexes. More information about Statistics on How to Create, Drop and update Statistics are covered here.
In this post, we will see how to retrieve the information on when a Statistics was last updated.
To retrieve this information, run the below query on one of the database.
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, name AS IndexName, Type_desc AS IndexType, TableType=Case When OBJECTPROPERTY(OBJECT_ID,'IsUserTable')=0 Then 'SystemTable' When OBJECTPROPERTY(OBJECT_ID,'IsUserTable')=1 Then 'UserTable' END, STATS_DATE(OBJECT_ID, index_id) AS StatsUpdatedDate 
FROM sys.indexes 
Where OBJECTPROPERTY(OBJECT_ID,'IsUserTable')=1 
Order by TableName

This query will list the indexes present on all of the user tables in a particular database and when their Statistics was last updated.

Revoke access for all Orphaned users in a server

When a database is moved or restored from one server to another server the login Id's stored in the master database do not align with the login Id's stored in the user database, which account for the orphaned users in the databases.


To get the list of Orphaned users in a database, execute the below query on the particular database.
Exec sp_change_users_login 'Report'


To cycle through all the databases in a server to find the orphaned users, execute the below query

Exec sp_MSforeachdb 'use ?;
Select db_name() as DatabaseName
Exec sp_change_users_login ''Report'''

To Revoke access for all the Orphaned Users in a server, Run the below script

Create a History table in tempdb or in any other DB as required or if required.


Create Table tempdb..OrphUsers_Histroy
(Username nvarchar(250),
userSID varbinary (85),
SQLText nvarchar(500),
Fixedon datetime)


Select * from tempdb..OrphUsers_Histroy

Exec sp_MSforeachdb 'use ?;


Create Table tempdb..OrphUsers 
(Username nvarchar(250),
userSID varbinary (85),
SQLText nvarchar(500))


insert into tempdb..OrphUsers (Username,userSID) 
Exec sp_change_users_login ''Report''


update tempdb..OrphUsers set SQLTEXT=''Exec sp_revokedbaccess ''+username+''''


while (select COUNT(*) from tempdb..OrphUsers )<>0
Begin
declare @SQLTEXT nvarchar(500)
select @SQLTEXT=SQLText from tempdb..OrphUsers
exec sp_executesql @SQLTEXT


/* Remove this insert statement if you do not want to log history */ 
insert into tempdb..OrphUsers_Histroy
select Username,userSID,SQLText,getdate() from tempdb..OrphUsers where SQLTEXT=@SQLTEXT


delete from tempdb..OrphUsers where SQLTEXT=@SQLTEXT
END


drop table tempdb..OrphUsers '

Select * from tempdb..OrphUsers_Histroy

Query to find Dependant Objects

Select DISTINCT (OBJECT_SCHEMA_NAME(id)+'.'+ OBJECT_NAME(id)) AS [Object Name] ,(OBJECT_SCHEMA_NAME(depid)+'.'+ OBJECT_NAME(depid)) AS [Is Dependant on], OBJECTPROPERTYEX(id,'BaseType') AS [Object Type],OBJECTPROPERTYEX(depid,'BaseType') AS [Object Type of Is Dependant on] From sys.sysdepends Where OBJECTPROPERTYEX(id,'IsMSShipped')=0 -- To get only user created object