Script to find dependent objects

I found that sp_depends does not guarantee correct results, Since Sql 2008 have provided useful way to get correct results. Here is sample script.

For finding Referencing Entities. Typically usage is to find out where does particular table is used in entire database.
SELECT  distinct referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities ('Object_Name', 'object');

For finding Referenced Entities.Typicall usage is to find out tables which are used in stored procedure.
SELECT  distinct referenced_schema_name, referenced_entity_name
FROM sys.dm_sql_referenced_entities ('Object_Name', 'object');

Script to Get Rowcount and Data Size of all the user tables

Script to Get rowcount and Data Size

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11), reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' ' ;
WITH TABLES_ROWS_AND_SIZE AS (
 SELECT TableName , NumberOfRows = CONVERT(bigint,rows) , SizeinKB = CONVERT(bigint,left(reserved,len(reserved)-3))
FROM #RowCountsAndSizes )

select TableName , NumRows = REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,NumberOfRows),1), '.00','')
, SizeKB = REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,SizeinKB),1), '.00','')
FROM TABLES_ROWS_AND_SIZE
 ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName
DROP TABLE #RowCountsAndSizes


Alternate way to get row count of existing tables.

SELECT o.name,
  ddps.row_count
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id
WHERE i.index_id < 2  AND o.is_ms_shipped = 0
ORDER BY o.NAME

Script to Check Sql Job Status

/***
When there is need to check for job is completed or not before triggering next process or job. we can use this script.

Setp_id is key column which provides details on Job Status.


**/

USE MSDB
SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */  
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
           ,cast(run_date as varchar(10)) as Run_Date
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'  
          END [Job Outcome]
          ,step_id
          ,step_name
          ,run_status
FROM   sysjobhistory SJH  
JOIN   sysjobs SJ  
ON     SJH.job_id=sj.job_id  
WHERE  step_id=0  
AND    cast(run_date as varchar(10)) = CAST(getdate() as DATE)
AND name = 'Jobname'

ORDER BY name,run_date,run_time

Great Microsoft BI Blogs and Resources

For All MSBI

Sql Server Central

BI Monkey

Code Project

SQL Server 2008R2 Videos

SSIS

F5 Debug Blog