sqlserver

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%'

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

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 ...

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

How to use a select in update statement using MSSQL

 

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

Sample SQL

UPDATE TableOne
SET
    t1.ColumnOne = t2.ColumnOne,
    t1.ColumnTwo = t2.ColumnTwo
FROM
    TableOne AS t1
INNER JOIN
    TableTwo AS t2
ON
    TableOne.id = TableTwo.id

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)