Must have SQL Queries – SQL Server

Top 10 Must have t-Sql Queries.

—————————————————–
— Record count for all tables
—————————————————–
select
schema_name(a.schema_id) + ‘.’ + a.name,
sum(b.rows) as ‘rowcount’
from sys.objects a
inner join sys.partitions b
on a.object_id = b.object_id
where a.type = ‘u’
group by a.schema_id, a.name
order by 2 desc
go
—————————————————–
— Search string in database objects
—————————————————–
select
routine_name,
routine_definition,
routine_type
from information_schema.routines
where routine_definition like ‘%string%’
–and routine_type=’procedure’–‘function’
order by routine_name

—————————————————–
— Search string in data
—————————————————–
declare
@search_string  varchar(100),
@table_name     sysname,
@table_schema   sysname,
@column_name    sysname,
@sql_query     varchar(2000)

set @search_string = ‘A’
—————————————————–
declare tables_cursor cursor for select table_schema, table_name
from information_schema.tables where table_type = ‘base table’
open tables_cursor
fetch next from tables_cursor into @table_schema, @table_name
while (@@fetch_status = 0)
begin
—————————————————–
declare columns_cursor cursor for
select column_name
from information_schema.columns
where table_schema = @table_schema
and table_name = @table_name
and collation_name is not null
open columns_cursor
fetch next from columns_cursor into @column_name
while (@@fetch_status = 0)
begin
set @sql_query = ‘if exists (select * from ‘
+ quotename(@table_schema) + ‘.’
+ quotename(@table_name)
+ ‘ where ‘
+ quotename(@column_name)
+ ‘ like ”%’
+ @search_string + ‘%”) print ”’
+ quotename(@table_schema) + ‘.’
+ quotename(@table_name) + ‘, ‘
+ quotename(@column_name) + ””
execute(@sql_query)
fetch next from columns_cursor into @column_name
end
close columns_cursor
deallocate columns_cursor
—————————————————–
fetch next from tables_cursor into @table_schema, @table_name
end
close tables_cursor
deallocate tables_cursor
—————————————————–

—————————————————–
— Index fragmentation check
—————————————————–
select
object_id as objectid,
index_id as indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
from sys.dm_db_index_physical_stats (db_id(), null, null , null, ‘limited’)
where avg_fragmentation_in_percent > 5.0
and index_id > 0;

—————————————————–
— Statistics check
—————————————————–
select
o.name ‘table name’,
i.name ‘index name’,
i.type ‘index type’,
stats_date(i.[object_id], i.[index_id]) ‘last statistics update’
from sys.indexes i,
sys.objects o
where i.[object_id]=o.[object_id]
and o.type=’u’ and i.name is not null
and stats_date(i.object_id, i.index_id) < dateadd(day, -1, getdate())
order by ‘last statistics update’ desc

—————————————————–
— Information about current users, sessions, and processes
—————————————————–
sp_who2

—————————————————–
— Information about process
—————————————————–
DBCC INPUTBUFFER (SPID)

— Database size information
—————————————————–
sp_spaceused

—————————————————–
— Database table size information
—————————————————–
sp_spaceused ‘table name’

—————————————————–
— Database object Information
—————————————————–
sp_help ‘table name or procedure or function’;

Happy Coding.

Thanks for reading 🙂

Keep reading , share your thoughts, experiences. Feel free to contact us to discuss more. If you have any suggestion / feedback / doubt, you are most welcome.

Stay tuned on Knowledge-Junction, will come up with more such articles / news
Local Business Directory, Search Engine Submission & SEO Tools