MSSQL BASIC QUERY
Server Information
=====================
SELECT
CAST( SERVERPROPERTY( 'MachineName' ) AS varchar( 30 ) ) AS MachineName ,
CAST( SERVERPROPERTY( 'ServerName' ) AS varchar( 30 ) ) AS ServerName ,
SYSTEM_USER as SystemUser,
HOST_ID() as HostID ,
HOST_NAME() as HostName,
@@version as SQLversion
System Server Details
======================
exec sp_server_info
Server Statistics
=====================
exec sp_monitor
Configuration Information
===========================
exec sp_configure
Database Details
===================
Select
@@SERVICENAME as Servicename,
@@SPID AS 'ID', USER AS 'User Name',
user_id () as Userid ,
CURRENT_USER as CurrentUser ,
SESSION_USER as sessionuser,
@@connections as Connections ,
@@max_connections AS "Max Connections",
DB_NAME() as DatabaseName ,
APP_NAME() as ApplicationName ,
@@version as version,
CAST( SERVERPROPERTY( 'InstanceName' ) AS varchar( 30 ) ) AS Instance ,
CAST( SERVERPROPERTY( 'ProductVersion' ) AS varchar( 30 ) ) AS ProductVersion ,
CAST( SERVERPROPERTY( 'ProcessID' ) AS varchar( 30 ) ) AS ProcessID,
CAST( SERVERPROPERTY( 'ProductLevel' ) AS varchar( 30 ) ) AS ProductLevel ,
CAST( SERVERPROPERTY( 'NumLicenses' ) AS varchar( 30 ) ) AS Numlicenses,
CAST( SERVERPROPERTY( 'Edition' ) AS varchar( 30 ) ) AS Edition ,
(CASE SERVERPROPERTY( 'EngineEdition')
WHEN 1 THEN 'Personal or Desktop'
WHEN 2 THEN 'Standard'
WHEN 3 THEN 'Enterprise'
END) AS EngineType ,
CAST( SERVERPROPERTY( 'LicenseType' ) AS varchar( 30 ) ) AS LicenseType , SERVERPROPERTY ( 'NumLicenses' ) AS #Licenses
Current Database Time
======================
select 'The current time is: '+ convert(char(30), CURRENT_TIMESTAMP)
Database Availability
=======================
select name,dbid,sid,mode,status ,crdate, category, cmptlevel 'Compatibility level' , filename , version from master..sysdatabases
Database Size
================
Use master
exec sp_databases
Database Datafile Growth
===========================
select convert(varchar(100),cntr_value/1024) as 'Database Size (MB)' from sysperfinfo (nolock) where
object_name ='SQLServer:Databases' and
counter_name ='Data File(s) Size (KB)' and
instance_name ='_Total'
System files info
=================
select fileid, groupid, size,maxsize, growth,name,filename from sysfiles
Database Size & Info
====================
exec sp_helpdB
Database Space Information:
===========================
Use master
sp_spaceused
Device Info
==============
Use Master
select name,size,phyname from sysdevices
Disk Read/Write Info
=====================
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As Of'
Go
Current Session user
=====================
SELECT 'This session''s current user is: '+ Session_user , 'The current user is: '+ convert(char(30), CURRENT_USER)
User Connection Info
=====================
select convert(varchar(100),cntr_value) from sysperfinfo (nolock) where
object_name = 'SQLServer:General Statistics' and counter_name ='User Connections'
Display all active processes
==============================
USE master
EXEC sp_who 'active'
List all current processes
================================
USE master
EXEC sp_who
Table info
=============
select
b.name "User Name" ,
a.name" Table Name" ,
a.uid " User ID" ,
hasdbaccess "database access",
islogin "Windows/SQL Server User ",
isntname"Windows group/user",
isntgroup"Windows user",
isntuser"Windows user",
issqluser "SQL Server user" ,
isaliased"aliased to another user",
issqlrole "SQL Server role",
isapprole "application role"
from
sysobjects a ,
sysusers b
where
a.xtype ='U' and a.uid=b.uid and
b.name='dbo'
Index Details
=================
select a.name "IndexName" ,a.minlen " MinSizeof Rows", a.keycnt " Noof Keys" ,
a.groupid "FileGroup",a.dpages "DataPages" ,a.used "Used otal Page" , a.rowcnt "RowCount",
a.rowmodctr "Total I/D/U", a.xmaxlen "MaxSizeofRow" ,a.rows "NoofRows", b.uid "UserID" ,b.name "UserName"
from sysindexes a ,sysusers b where b.name ='POGUTTU' and a. indid > 1
Cluster Index Info
===================
select a.name "IndexName" ,a.minlen " MinSizeof Rows", a.keycnt " Noof Keys" ,a.groupid "FileGroup",a.dpages "DataPages" ,
a.used "Used otal Page" , a.rowcnt "RowCount",a.rowmodctr "Total I/D/U", a.xmaxlen "MaxSizeofRow" ,a.rows "NoofRows", b.uid "UserID" ,
b.name "UserName" from sysindexes a ,sysusers b where b.name ='dbo' and a. indid =1
Fragmentation Info
=======================
SET NOCOUNT ON
USE poguttu
DBCC SHOWCONTIG WITH ALL_INDEXES
GO
Procedure Info
=================
select
b.name "User Name" ,
a.name" Table Name" ,
a.uid " User ID" ,
hasdbaccess "database access",
islogin "Windows/SQL Server User ",
isntname"Windows group/user",
isntgroup"Windows user",
isntuser"Windows user",
issqluser "SQL Server user" ,
isaliased"aliased to another user",
issqlrole "SQL Server role",
isapprole "application role"
from
sysobjects a ,
sysusers b
where
a.xtype ='P' and
a.uid=b.uid and
b.name='dbo'
User Info
=================
Select * from sysusers
lists all users in the current database
========================================
EXEC sp_helpuser
List all locks
==================
USE master
EXEC sp_lock
Log File size Growth
======================
declare @log_size float(53)
declare @log_space_used float(53)
declare @total decimal(20,4)
select @log_size = cntr_value from master.dbo.sysperfinfo where counter_name ='Log File(s) Size (KB)'
select @log_space_used = cntr_value from master.dbo.sysperfinfo where counter_name ='Log File(s) Used Size (KB)'
set @total = (@log_space_used/@log_size) * 100
select 'Using ' + convert(varchar(20),@total) + '% (' + convert(varchar(20),(@log_space_used)/1024) + ' MB) of ' + convert(varchar(20),(@log_size)/1024) + ' MB' as 'Report'
