Friday, June 19, 2015

get numbers of table in database used by front end application or any user


SELECT
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
FROM
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
ORDER BY last_user_seek DESC
Folowing command will tell the procedure name executed

SELECT TOP 1000 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
,DB_NAME(d.database_id) AS [SCHEMA_NAME]
FROM sys.dm_exec_procedure_stats AS d
where DB_NAME(d.database_id) = 'databaseName'
ORDER BY [last_execution_time] DESC;

Thursday, June 18, 2015

update sort order column in datatable.

If we are sorting based on SorteOrder column in Datatable. many time we need to organize columns based on certain condition following is command to update sort Oder column.

UPDATE Table1
SET SortOrder = vx.RowNum
FROM Table1
CROSS APPLY
(

-- step 1 first execute this comamnd and once you agreed for sortign execute update
SELECT ColumnNameToSetSorOrdre,PrimaryKey, ROW_NUMBER() OVER (ORDER BY ISNULL(ColumnNameToSetSorOrdre, 9999999), ColumnNameToSetSorOrdre)  AS RowNum
FROM Table1
WHERE IsActive = 0
-- AND other conditions
) vx
WHERE vx.PrimaryKey = Table1.PrimaryKey
-- and Other conditions If requried

BR
Mahesh

Thursday, June 11, 2015

How to get number of rows in the data table


DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString