Category: T-SQL

9 Comments

SQL Server – Get Size of All Databases in MB and GB

  By , ,

The script below will retrieve the size of all your databases in MB and GB.

 SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Find Owners of SQL Server Agent Jobs Using TSQL

  By , ,

Find out who are the owners of the SQL agent jobs by running the script below.

SELECT    
    J.name AS [Job Name]
    ,L.name AS [Job Owner]
FROM
    msdb.dbo.sysjobs_view J
    INNER JOIN master.dbo.syslogins L
        ON J.owner_sid = L.sid
GO

No comments

SQL Server – Check if “Enforce Password Policy” is On Using TSQL

  By , ,

The T-SQL query below will check if “Enforce Password Policy” is On. A value of 1 under is_policy_checked column indicates that it is turned on. Otherwise, it is disabled.

select name, is_policy_checked 
from sys.sql_logins

No comments

SQL Server – View FileNames and Paths Using TSQL

  By , ,

The T-SQL script below can be used to check the Filenames and paths of your data and log files.

SELECT 
    DB_NAME ([database_id]) AS [Database Name]
    ,name
    ,physical_name
    ,type_desc
    ,state_desc
    ,CONVERT(bigint, size/128.0) AS [Total Size in MB]
    ,CONVERT(decimal(18,2), size/131072.0) AS [Total Size in GB]
FROM
    sys.master_files WITH(NOLOCK)
WHERE
    [database_id] > 4
    AND [database_id] <> 32767
    OR [database_id] = 2
ORDER BY DB_NAME ([database_id]) OPTION(RECOMPILE)

Output

No comments

SQL Server – Check SQL Server Error Logs via T-SQL

  By , , ,

Below is a script​​ to check SQL Server Error​​ logs.

 

 

declare​​ @Time_Start​​ datetime;

declare​​ @Time_End​​ datetime;

set​​ @Time_Start=getdate()-2;

set​​ @Time_End=getdate();

-- Create the temporary table

CREATE​​ TABLE​​ #ErrorLog​​ (logdate​​ datetime

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ,​​ processinfo​​ varchar(255)

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ,​​ Message​​ varchar(MAX))

-- Populate the temporary table

INSERT​​ #ErrorLog​​ (logdate,​​ processinfo,​​ Message)

 ​​ ​​​​ EXEC​​ master.dbo.xp_readerrorlog​​ 0,​​ 1,​​ null,​​ null​​ ,​​ @Time_Start,​​ @Time_End,​​ N'desc';

-- Filter the temporary table

SELECT​​ LogDate,​​ Message​​ FROM​​ #ErrorLog

WHERE​​ (Message​​ LIKE​​ '%error%'​​ OR​​ Message​​ LIKE​​ '%failed%')​​ AND​​ processinfo​​ NOT​​ LIKE​​ 'logon'

ORDER​​ BY​​ logdate​​ DESC

-- Drop the temporary table​​ 

DROP​​ TABLE​​ #ErrorLog

 

 

No comments

SQL Server – Change Database Owner

  By , ,

Below is the T-SQL script to change DB owner.​​ 

 

EXEC​​ sp_changedbowner​​ 'owner_name';

 

No comments

SQL Server – Get the Table Structure Using T-SQL

  By , ,

When you are writing a SELECT statement and you just want to limit the number of columns, you would use the​​ sp_help​​ stored procedure to research the underlying table structure as shown​​ below.

 

​​ 

 

 

The​​ sp_help​​ system stored procedure will return all the data about a particular table when you pass the table name into the stored procedure.

 

But If you don't want to sort through all that information, it is possible to get only the names of the columns by executing a query that selects all columns with a false​​ WHERE​​ clause as shown below.

 

SELECT​​ *

FROM​​ [HumanResources].[Employee]

WHERE​​ 1=0

 

 

 

This query tells SQL Server to return all the columns and rows where one equals zero. Because one can never equal zero, SQL Server will return the column names from the table with no data.