Check SQL Server Table/Index Space

select top 50
    -- one page is 8K
    sum(s.page_count)/125.00 as [Size(MB)]
    -- record_count is at index level, can not do total sum here
    -- also using limited level with dm_db_index_physical_stats will give us null value
    --,sum(isnull(s.record_count, 0)) as total_record_Count
    ,d.name as DataBaseName
    ,sc.name as SchemaName
    ,o.name as TableName
    ,i.type_desc as IndexType
    ,i.name as IndexName
from    --sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') s
        sys.dm_db_index_physical_stats(db_id(), null, null, null, 'limited') s
        inner join sys.databases d on d.database_id=s.database_id
        inner join sys.objects o on o.object_id=s.object_id
        left join sys.indexes i on i.object_id=s.object_id and i.index_id=s.index_id
        inner join sys.schemas sc on sc.schema_id=o.schema_id
where    -- specfic index type (0 : heap, 1: clustered, 2: non-clusterd)
        i.type in (0,1,2)
        --and i.type in (0,1)
group by d.name, sc.name, o.name, i.name, i.type_desc
order by 1 desc
--go
--sp_spaceused 'MyTableName', @updateusage = N'TRUE'

Sql Server Table Row Count

SELECT
 s.Name as SchemaName
 ,t.NAME AS TableName
 ,sum(p.rows) as RowCounts
    FROM sys.tables t INNER JOIN sys.indexes i
   ON t.OBJECT_ID = i.object_id
   INNER JOIN sys.partitions p
   ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
   INNER JOIN sys.schemas s
   ON s.schema_id=t.schema_id
 WHERE -- heap or clustered
   i.type IN (0, 1)
 GROUP BY s.Name, t.NAME, i.object_id, i.index_id, i.name
 -- use this to check list empty table ONLY
 --HAVING SUM(p.rows) = 0
 order by 1,2

Get SQL Statements with "table scan" in cached query plan with filter (inspired by Olaf Helper)

reference:
From http://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-0622af19

http://sqlfascination.com/2010/03/10/locating-table-scans-within-the-query-cache/

http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/


Description:
"Table scan" (and also "Index scan") can cause poor performance, especially when they are performed on large tables.
To identify queries causing such scans you can use the SQL Profiler with the events "Scans" => "Scan:Started" and "Scan.Stopped".
Other option is to analyse the cached query plans.
This Transact-SQL statements filters the cached query plans for existing table scan operators and returns the statement and query statistics.
An additional filter is set on the attribute "EstimateRows * @AvgRowSize" = "Estimate size" to filter out scans on small tables.
Note: The xml data of the cached query plans is not indexed in the DMV, therefore the query can run up to several minutes.
Works with SQL Server 2005 and higher versions in all editions.
Requires VIEW SERVER STATE permissions.

note: this is imporved script to fix some potential performance issues:

--------------------------------------
/*
"Table scan" (and also "Index scan") can cause poor performance, especially when they are performed on large tables.
To identify queries causing such scans you can use the SQL Profiler with the events "Scans" => "Scan:Started" and "Scan.Stopped".
Other option is to analyse the cached query plans.
This Transact-SQL statements filters the cached query plans for existing table scan operators and returns the statement and query statistics.
An additional filter is set on the attribute "EstimateRows * @AvgRowSize" = "Estimate size" to filter out scans on small tables.
Note: The xml data of the cached query plans is not indexed in the DMV, therefore the query can run up to several minutes.
Works with SQL Server 2005 and higher versions in all editions.
Requires VIEW SERVER STATE permissions.
*/
-- Get all SQL Statements with "table scan" in cached query plan
;
set transaction isolation level read uncommitted
declare @MinElapsedTime int, @MinExecutionCount int, @MinScanSize int
----------------------------------------------------------------------------------
-- change the values here
-- run at least 5 times and total time elasped at least 5 seconds
--
select @MinElapsedTime=5000, @MinExecutionCount=5, @MinScanSize=5000
-----------------------------------------------------------------------------------
if object_id('tempdb..#EQS') is not null drop table #EQS

SELECT EQS.plan_handle
 ,SUM(EQS.execution_count) AS ExecutionCount
 ,SUM(EQS.total_worker_time) AS TotalWorkTime
 ,SUM(EQS.total_logical_reads) AS TotalLogicalReads
 ,SUM(EQS.total_logical_writes) AS TotalLogicalWrites
 ,SUM(EQS.total_elapsed_time) AS TotalElapsedTime
 ,MAX(EQS.last_execution_time) AS LastExecutionTime
 into #EQS
 FROM sys.dm_exec_query_stats AS EQS
 GROUP BY EQS.plan_handle
 HAVING (SUM(EQS.total_elapsed_time)>@MinElapsedTime AND SUM(EQS.execution_count)>@MinExecutionCount)
--select * from #EQS
create unique clustered index IDX_EQS_handle on #EQS(plan_handle)

if object_id('tempdb..#ECP') is not null drop table #ECP
;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
 --top 100
 RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID
 ,RelOp.op.value(N'@NodeId', N'int') AS OperationID
 ,RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator
 ,RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator
 ,RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost
 ,RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO
 ,RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU
 ,RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows
 ,RelOp.op.value(N'@AvgRowSize ', N'float') AS AverageRowSize
 ,Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Database', N'varchar(256)') 
   ,RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Database', N'varchar(256)')
   ,RelOp.op.value(N'IndexScan[1]/Object[1]/@Database', N'varchar(256)')
   ,'Unknown') as DatabaseName
 ,Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Schema', N'varchar(256)')
   ,RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Schema', N'varchar(256)')
   ,RelOp.op.value(N'IndexScan[1]/Object[1]/@Schema', N'varchar(256)')
   ,'Unknown') as SchemaName
 ,Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Table', N'varchar(256)')
   ,RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Table', N'varchar(256)')
   ,RelOp.op.value(N'IndexScan[1]/Object[1]/@Table', N'varchar(256)')
   ,'Unknown') as ObjectName
 ,ECP.plan_handle
 ,ST.TEXT AS QueryText
 ,QP.query_plan AS QueryPlan
 ,ECP.cacheobjtype AS CacheObjectType
 ,ECP.objtype AS ObjectType
 ,QP.dbid
 ,QP.objectid
 ,ECP.usecounts
 into #ECP
 FROM sys.dm_exec_cached_plans ECP
   CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) ST
   CROSS APPLY sys.dm_exec_query_plan(ECP.plan_handle) QP
   CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
   INNER JOIN #EQS EQS
   ON ECP.plan_handle = EQS.plan_handle
 -- can be filtered later , but ...
 -- used multiple times
 WHERE ECP.usecounts>1
   AND RelOp.op.value(N'@PhysicalOp', N'varchar(50)')  IN ('Clustered Index Scan', 'Table Scan' ,'Index Scan')
   AND (RelOp.op.value(N'@AvgRowSize ', N'float')*RelOp.op.value(N'@EstimateRows', N'float'))>@MinScanSize

--create unique clustered index IDX_ECP_handle on #ECP(plan_handle)
create clustered index IDX_ECP_handle on #ECP(plan_handle)
--select plan_handle, count(*) from #ECP group by plan_handle  order by 2 desc--plan_handle

SELECT
 --top 1
 -- query plan related object
 DB_NAME(ECP.[dbid]) AS [DatabaseName]
 ,OBJECT_NAME(ECP.[objectid], ECP.[dbid]) AS [ObjectName]
 -- scanned objects
 -- from query plan scan part
 ,ECP.SchemaName as ScannedSchemaName
 ,ECP.ObjectName as ScannedObjectName
 ,ECP.DatabaseName as ScannedDatabaseName
 ,ECP.CacheObjectType
 ,EQS.[TotalElapsedTime]/6000.00 as [TotalElapsedTime(min)]
 ,EQS.[ExecutionCount]
 ,EQS.[TotalWorkTime]/6000.00 as [TotalWorkTime(min)]
 ,ECP.EstimatedRows*AverageRowSize/8096.00 AS [EstimatedScanSize(Page)]
 ,EstimatedCost
 ,EstimatedIO
 ,ECP.EstimatedRows
 ,ECP.AverageRowSize
 ,EQS.[TotalLogicalReads]
 ,EQS.[TotalLogicalWrites]
 ,EQS.[LastExecutionTime]
 ,ECP.QueryText     
 ,ECP.QueryPlan
 into #FullResult
 FROM #ECP AS ECP
   INNER JOIN #EQS EQS
   ON ECP.plan_handle = EQS.plan_handle     

select * from #FullResult ORDER BY 1,2,3,4,5,6,7 desc
if object_id('tempdb..#EQS') is not null drop table #EQS
if object_id('tempdb..#ECP') is not null drop table #ECP
if object_id('tempdb..#FullResult') is not null drop table #FullResult

--------------------------------------


-- Get all SQL Statements with "table scan" in cached query plan

;WITH
XMLNAMESPACES
(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
,EQS AS
(SELECT EQS.plan_handle
,SUM(EQS.execution_count) AS ExecutionCount
,SUM(EQS.total_worker_time) AS TotalWorkTime
,SUM(EQS.total_logical_reads) AS TotalLogicalReads
,SUM(EQS.total_logical_writes) AS TotalLogicalWrites
,SUM(EQS.total_elapsed_time) AS TotalElapsedTime
,MAX(EQS.last_execution_time) AS LastExecutionTime
FROM sys.dm_exec_query_stats AS EQS
GROUP BY EQS.plan_handle)
SELECT EQS.[ExecutionCount]
,EQS.[TotalWorkTime]
,EQS.[TotalLogicalReads]
,EQS.[TotalLogicalWrites]
,EQS.[TotalElapsedTime]
,EQS.[LastExecutionTime]
,ECP.[objtype] AS [ObjectType]
,ECP.[cacheobjtype] AS [CacheObjectType]
,DB_NAME(EST.[dbid]) AS [DatabaseName]
,OBJECT_NAME(EST.[objectid], EST.[dbid]) AS [ObjectName]
,EST.[text] AS [Statement]
,EQP.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_cached_plans AS ECP
INNER JOIN EQS
ON ECP.plan_handle = EQS.plan_handle
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
WHERE EQP.[query_plan].exist('data(//RelOp[@PhysicalOp="Table Scan"][@EstimateRows * @AvgRowSize > 50000.0][1])') = 1
-- Optional filters
AND EQS.[ExecutionCount] > 1 -- No Ad-Hoc queries
AND ECP.[usecounts] > 1
ORDER BY EQS.TotalElapsedTime DESC
,EQS.ExecutionCount DESC;