Get count of multiple tables using T-SQL – SQL Server

I was asked by my coworker get row count of tables with a particular column. So I came up with this little script to get it quickly using T-SQL.

DECLARE @tableName VARCHAR(255)
DECLARE @count smallint
DECLARE @stmt NVARCHAR(4000);

##Create insert statement into temp.

SET @stmt = 'INSERT INTO #tmpTable (tableNames, recCount)
SELECT ''** tableName **'', count(*) from ** tableName ** WHERE 1=1 and
= ''value''';

##Create temp table

CREATE table #tmpTable(
tableNames VARCHAR(255),
recCount smallint
)

##Declare CURSOR for select statement from sys.columns

DECLARE db_cursor CURSOR FOR
select distinct OBJECT_NAME(object_id) from sys.columns
where 1=1
and name = '
'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tableName

##Use while loop to insert record count in temp table.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @stmt = REPLACE(@stmt, '** tableName **', @tableName);

EXEC sp_executesql @stmt;

SET @stmt = 'INSERT INTO #tmpTable (tableNames, recCount)
SELECT ''** tableName **'', count(*) from ** tableName ** WHERE 1=1 and
= ''value''';

FETCH NEXT FROM db_cursor INTO @tableName

END

Close db_cursor
DEALLOCATE db_cursor

##Get all the tables with count

select * from #tmpTable

#Drop temp table.

drop table #tmpTable

sp_WhoIsActive

Find out what’s going in SQL server using sp_WhoIsActive.

##Running with this parameter will return column “sql_command”, which shows queries running with parameter values to troubleshoot slow running queries.

sp_WhoIsActive @get_outer_command = 1

##This will filter on the session

sp_WhoIsActive @filter_type =’session’, @filter = 431

ADR Commands Oracle

###Start ADR

adrci

###shows errors in log

SHOW PROBLEM

###Shows last 10 entries of alert file

SHOW ALERT -TAIL

###Display all trace files

SHOW TRACEFILE

###Display trace file location related to incident

SHOW TRACEFILE -I 1234

###Shows incidents in 11g above

SHOW INCIDENT

###Show details of particular incident

SHOW INCIDENT -MODE DETAIL -P “INCIDENT_ID=1234″