SQL Server – How to Find Usage of Each Object in Database

database-designerwinsql serversql-server-2008

I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all relations inside the code.

So my mission is to identify usages, relationships and make a graphic model, which I think I will use ERwin7 to do it.

I stated today taking every table name and searching it in the source-code and sql metadata. I open all those files in separated instances of Notepad++ and search to see if anyone really uses that table in the source-code or in any stored procedure or function. But there are 1500+ c# files and 857 objects in the database.

Is there a better way to do that?
Is there a tool to tell me where each table is used in the code and in the database without the need of Ctrl+F for each table name or other objects I will also check?
Is ER-win a reasonable choice for doing the re-modeling?

Best Answer

I'm afraid I can't help with the C# code or Erwin but you can use this query for the SQL Code

SELECT * FROM sys.dm_sql_referencing_entities('dbo.ABC', 'OBJECT')

The above query will return a list of all code objects (SPs, Functions etc) in the database that reference the object named in the initial parameter. The two part name is required. It will not unfortunately list objects that are not in the current database but it should get you started.

If you need it you can also go the other direction.

SELECT * FROM sys.dm_sql_referencing_entities('dbo.ABC', 'OBJECT')

In this case the first parameter dbo.ABC would be a piece of code (an SP for example) and the DMO will return a list of all objects used by that SP (or whatever). From what I can tell this one WILL return objects that are in different databases or servers.

I discussed these and some other (older) options here: http://sqlstudies.com/2012/10/26/how-do-i-find-all-stored-procedures-or-views-that-use-a-specific-table/

Related Solutions

Sql-server – How to check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008)

These DBCC CHECKCONSTRAINTS commands can be run as follows:

DBCC CHECKCONSTRAINTS (TableName) - checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query runs
DBCC CHECKCONSTRAINTS check only enabled constraints

Keep in mind that SQL Server needs to read through all of your data to check for the constraints, so be careful not to run this across the board for your large or very busy databases. This should be something that is run off hours and also for very large databases you should run this either at a table level or a constraint level.

SQL Server – How to List the Last Backup File of a Database

This is just a matter of using dynamic SQL to process your data. You can also leverage msdb.dbo.backupset and msdb.dbo.backupfamily to find your most recent full backups. This script will get the most recent full backup file location for each database and use it to run your cursor:

declare @dbname sysname
    ,@backupfile nvarchar(max)
    ,@sqlcmd nvarchar(max)

declare recscan cursor fast_forward 
for
select
    bs.database_name
    ,bm.physical_device_name
from
    msdb.dbo.backupset bs
    join msdb.dbo.backupmediafamily bm on (bs.media_set_id = bm.media_set_id)
    join (select database_name
            ,max(backup_finish_date) last_backup
          from msdb.dbo.backupset
          where type = 'D'
          group by database_name) lb on (bs.database_name = lb.database_name and bs.backup_finish_date = lb.last_backup)
where type = 'D'

open recscan
fetch next from recscan into @dbname,@backupfile

while @@fetch_status != -1
begin
    PRINT 'Validating '+@dbname
    select @sqlcmd = N'restore verifyonly from disk = '''+@backupfile+''';'

    exec sp_executesql @sqlcmd

    fetch next from recscan into @dbname,@backupfile
end

close recscan
deallocate recscan

Best Answer

Related Solutions

Sql-server – How to check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008)

SQL Server – How to List the Last Backup File of a Database

Related Question