Tags » SQL Query

TSQL to find Deadlock count in Azure Server by date


;WITH CTE AS (
SELECT * FROM sys.event_log
WHERE event_type = 'deadlock')
SELECT convert(DATE,start_time) as Day, database_name, count(*) as Deadlock_count FROM CTE
GROUP BY convert(DATE,start_time), database_name
ORDER BY 1

--

SQL

Check IMEI Number by Luhn algorithm.

check digit (d9) = mod 10

The check digit is validated in three steps:

Starting from the right, double a digit every two digits (e.g., 5 → 10). 131 more words

SQL QUERY

Sql Query

1.

SQL> select onum,amount,odate from order1;

ONUM AMOUNT ODATE

—– ——— ———

3001 18.69 10-MAR-99

3002 767.19 10-MAR-99

3003 1900.1 10-MAR-99

3004 5160.45 10-MAR-99

3005 1098.25 10-APR-99… 4,004 more words

Select On-hand Per Date In AX 2012 Using SQL Query

If you know database structure in AX 2012, you can directly select data from SQL Server. Of course select data from SQL Server are faster than select data from AX 2012 framework. 332 more words

Dynamics AX

Understanding the SQL Query Window

Knowing where to look to run a query is also extremely helpful to getting started.  There are three locations within SQL Server that you need to be aware of to get started. 158 more words

SQL

TSQL to find users and their access on a given database

TSQL to find users and their access on a given database

--
SELECT      dp1.name AS DatabaseRoleName,
            COALESCE(DP2.name, 'No members') AS DatabaseUserName
FROM        sys.database_principals AS dp1
LEFT  JOIN  sys.database_role_members AS drm
      ON    drm.role_principal_id = dp1.principal_id
LEFT  JOIN  sys.database_principals AS dp2
      ON    dp2.principal_id = drm.member_principal_id
WHERE       dp1.type = 'R'
ORDER BY    dp1.name 



SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc,   
    pe.permission_name, s.name + '.' + o.name AS ObjectName  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id  
JOIN sys.objects AS o  
    ON pe.major_id = o.object_id  
JOIN sys.schemas AS s  
    ON o.schema_id = s.schema_id;   

--
--
SQL

TSQL to find size of all databases on a Server - Azure

--
-- DatabZse Name and Database Size

select
       @@serverName as ServerName,
    db.name as DatabaseName,
       (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
from
       sys.databases db 
order by db.name 


-- DB Size and additional details including pool name 
select
       @@serverName as ServerName,
    db.name as DatabaseName, db.create_date, db.state_desc, 
   (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
,do.edition, do.service_objective, do.elastic_pool_name
from
       sys.databases db JOIN sys.database_service_objectives do on db.database_id = do.database_id
order by db.name 
--
--
SQL