How to get the current Database and Log Filesize using MSSQL

To get the current Database and Log Filesize using MSSQL you can use the snippet below. Tested using SQL Server 2012.

Sample MSSQL

with database_files_CTE as
(
    select
        name, type_desc, physical_name, size_mb = convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb = convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024) from sys.database_files
)
select name,type_desc as [type],physical_name as [path],size_mb as [Filesize],space_used_mb as [Filesize used],
case size_mb when 0 then 0 else convert(decimal(5, 2), space_used_mb / size_mb * 100) end as [Filesize used %]  from database_files_CTE;

Sample Output

Query Results Filesizes

Please rate this snippet

How to drop Primary Key of a Table using MSSQL

To drop Primary Key of a Table using MSSQL you can use the snippet below.

Sample MSSQL

ALTER TABLE tblTest
DROP CONSTRAINT PK_tblTest
GO

Please rate this snippet

How to search stored procedures using MS-SQL

To search stored procedures using MS-SQL you can use the snippet below.

Sample MSSQL

SELECT Name FROM sys.procedures WHERE name LIKE '%StoredProcName%'

Please rate this snippet

How to split a delimited string to multiple rows using MSSQL

To split a delimited string to multiple rows using MSSQL you can use the snippet below.

Sample MSSQL

CREATE FUNCTION dbo.StringSplit
(
    @Input       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (
		SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (
					SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@Input, Number, CHARINDEX(@Delimiter, @Input + @Delimiter, Number) - Number)))
					FROM (SELECT ROW_NUMBER() OVER (ORDER BY ao.[object_id])
					FROM sys.all_objects AS ao CROSS APPLY sys.all_objects) AS n(Number)
					WHERE Number <= CONVERT(INT, LEN(@Input))
					AND SUBSTRING(@Delimiter + @Input, Number, 1) = @Delimiter
				  ) AS item);
GO

Please rate this snippet

How to use select statement in a delete statement using MSSQL

To use select statement in a delete statement using MSSQL you can use the snippet below.

Sample MSSQL

DELETE TableOne
FROM   TableOne
       INNER JOIN TableTwo
               ON TableTwo.Id= TableOne.Id
                  AND ...
                       WHERE ...

Please rate this snippet

How to get the size of all tables in MSSQL

To get the size of all tables using MSSQL you can use the snippet below.

Sample MSSQL

SELECT 
    tab.NAME AS [Tablename],
    s.Name AS [Schema Name],
    part.rows AS [Count of Rows],
    SUM(a.total_pages) * 8 AS [Total Space in KB], 
    SUM(a.used_pages) * 8 AS [Used Space in KB], 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8  AS [Unused Space in KB]
FROM sys.tables tab
INNER JOIN sys.indexes idx ON tab.OBJECT_ID = idx.object_id
INNER JOIN sys.partitions part ON idx.object_id = part.OBJECT_ID AND idx.index_id = part.index_id
LEFT OUTER JOIN sys.schemas s ON tab.schema_id = s.schema_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
WHERE tab.NAME NOT LIKE 'dt%' AND tab.is_ms_shipped = 0 AND idx.OBJECT_ID > 255 
GROUP BY tab.Name, s.Name, part.Rows
ORDER BY 4 DESC

Please rate this snippet

How to get monday date of current week in MSSQL

To get the Monday date of the current week you can use the snippet below.

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AS Monday

Please rate this snippet

How to delete duplicate records using MSSQL

To delete duplicate records using MSSQL you can use the following snippet.
The table must have identity column, which will be used to identify the duplicate records.

DELETE
FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY ColumnWithDuplicatesName)

Please rate this snippet

How to use IF ELSE in an SQL Select query

In MSSQL IF ELSE is not supported inside the SQL-Query.
For a similiar behaviour you should use CASE WHEN keyword.

Sample Query

SELECT CASE 
            WHEN [Productnumber] LIKE '112%' THEN 'SOFTWARE'
			WHEN [Productnumber] LIKE '005%' THEN 'HARDWARE'
		    ELSE 'MISC' 
       END as [ProductType] 
FROM dbo.[Products]

Please rate this snippet

How to list all Databases in MSSQL

To list all Databases in MSSQL you can use the following snippet.

Sample MSSQL

SELECT name FROM sys.databases

SQL Server Version newer than MS SQL 2000 also support these StoredProcedures.

EXEC sp_databases

or

EXEC sp_helpdb

Please rate this snippet