Sunday, April 25, 2010

::SQL Helpers

I will add some of the useful sql functions:

::COLUMN NAMES in a Table

select name from sys.columns
where object_id = OBJECT_ID(N'Sales.Orders');

::Information about schema views

lists the user tables in the current database along with their schema names
select table_schema, table_name, IST.TABLE_TYPE, IST.TABLE_CATALOG
--where TABLE_TYPE = N'Base table'

::List of tables in the database
exec sys.sp_tables;

::Waiting and Blocking Processes
Run this query at another query page and check waiting processes again with sys.dm_tran_locks

  request_session_id                                  AS spid,
  resource_type                                       AS restype,
  resource_database_id                                AS dbid,
  DB_NAME(resource_database_id)                       as DBNAME,
  resource_description                                AS res,
  resource_associated_entity_id                       AS resid,
      when resource_type = 'OBJECT' then  OBJECT_NAME( resource_associated_entity_id) 
      ELSE 'N/A'
  END  as ObjectName,
  request_mode                                        AS mode,
  request_status                                      AS status
FROM sys.dm_tran_locks
order by spid, restype;

No comments:

Post a Comment

Let me know what you think?