Track User activity on a Sql Server

Track User Activity

Track the User activity on a SQL machine by using the following command and you can check theType of Command the user used

  1. The Login Time and the When the last batch got executed
  2. From which program the server got invoked
  3.  CPU and Memory using
  4. DB Usage
select * from master.dbo.sysprocesses where nt_username = 'Author'

 

Track User activity on a Sql Server was last modified: October 1st, 2017 by Justin Antony

SQL error details

SQL ERROR Tracking

SQL Error can be tracked by using the following keywords. This will fetch the information at run time on the event of any error occurs.

ERROR_SEVERITY()
ERROR_STATE()
ERROR_MESSAGE()
ERROR_NUMBER()

 

SQL error details was last modified: October 1st, 2017 by Justin Antony

List Table and Index details of a database

Table Index Details

List the table and index details of a database using the following query

select 
s.name + '.' + t.name as [Table], max(i.type_desc) as [Index Type]
from
sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), NULL, NULL, NULL,NULL ) AS PS
INNER JOIN 
sys.tables t on PS.object_id = t.object_id
INNER JOIN 
sys.schemas s on t.schema_id = s.schema_id
INNER JOIN 
sys.indexes i on t.object_id = i.object_id
where 
s.name <> 'tmp'
group by s.name + '.' + t.name

 

List Table and Index details of a database was last modified: October 1st, 2017 by Justin Antony

Schema and Table Name information from Database

Find Database Table Name SchemaName

Obtain the schema and table information using the following query

select
s.[name] as SchemaName
,s.[name] + '.' + t.[name] as ObjName
from 
sys.tables t
join
sys.schemas s on t.schema_id = s.schema_id

 

 

 

Schema and Table Name information from Database was last modified: October 1st, 2017 by Justin Antony

Collation in SQL Server

SQL SERVER COLLATION  

Collation determines how the data should be stored. This stored data can be retrieved in the same format for any execution or compare operation.

Collation can determine options like Case sensitive, Accent Sensitive etc.  We can find the collation which the database is assigned by  using the following query.

select name,collation_name from sys.databases

 

 

Collation in SQL Server was last modified: October 1st, 2017 by Justin Antony