|
Saturday, 28 January 2012 02:01 |
// Query to get all the datails for transaction locks in sql server
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
 Read more: |
|
Tuesday, 11 October 2011 10:52 |
CREATE VIEW dbo.vw_XL_ForeignReferences
AS
SELECT TOP (100) PERCENT FK.TABLE_NAME AS TargetTable, CU.COLUMN_NAME AS TargetColumn, PK.TABLE_NAME AS SourceTable,
PT.COLUMN_NAME AS SourceColumn, C.CONSTRAINT_NAME AS RelationName
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
(SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY TargetTable, TargetColumn, SourceTable, SourceColumn Read more: |
|
|
Tuesday, 11 October 2011 10:51 |
CREATE VIEW dbo.vw_XL_TableColumns
AS
SELECT TOP (100) PERCENT tabs.name AS TableName, col.name AS ColumnName, coltype.name AS TypeName, col.max_length AS MaxLength, col.precision, col.scale,
CASE WHEN col.name = pkCol.COLUMN_NAME THEN 'Y' ELSE 'N' END AS IsPrimaryKey, CASE WHEN col.is_nullable = 1 THEN 'Y' ELSE 'N' END AS IsNullable,
CASE WHEN col.is_identity = 1 THEN 'Y' ELSE 'N' END AS IsIdentity, CASE WHEN col.is_computed = 1 THEN 'Y' ELSE 'N' END AS IsComputed,
col.column_id AS ColumnOrder
FROM sys.columns AS col INNER JOIN
sys.tables AS tabs ON col.object_id = tabs.object_id INNER JOIN
sys.systypes AS coltype ON coltype.xtype = col.system_type_id LEFT OUTER JOIN
(SELECT c.COLUMN_NAME, c.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS pk INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c ON pk.TABLE_NAME = c.TABLE_NAME AND
pk.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE (pk.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS pkCol ON pkCol.TABLE_NAME = tabs.name AND pkCol.COLUMN_NAME = col.name
ORDER BY TableName, ColumnOrder Read more: |
|
Monday, 03 October 2011 06:50 |
SELECT ISNULL(A.SECURITYROLEID,'') AS SECURITYROLEID, ISNULL(M.SECURITYROLENAME,'') AS SECURITYROLENAME,ISNULL(M.SECURITYROLEDESC,'') AS SECURITYROLEDESC,
--ISNULL(O.SECURITYTASKID,'') AS SECURITYTASKID, ISNULL(T.SECURITYTASKNAME,'') AS SECURITYTASKNAME, --ISNULL(T.SECURITYTASKDESC,'') AS SECURITYTASKDESC,
R.DSPLNAME--,R.PRODNAME, R.TYPESTR, R.RESTECHNAME, R.DICTID, R.SECRESTYPE, R.SECURITYID
FROM DYNAMICS.dbo.SY09400 R
FULL JOIN DYNAMICS.dbo.SY10700 O ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID
FULL JOIN DYNAMICS.dbo.SY09000 T ON T.SECURITYTASKID = O.SECURITYTASKID
FULL JOIN DYNAMICS.dbo.SY10600 A ON A.SECURITYTASKID = T.SECURITYTASKID
FULL JOIN DYNAMICS.dbo.SY09100 M ON M.SECURITYROLEID = A.SECURITYROLEID
WHERE A.SECURITYROLEID like '%PUR%'
--WHERE R.DSPLNAME = 'Sales Transaction Entry' Read more: |
|