To search stored procedures using MS-SQL you can use the snippet below.
Sample MSSQL
SELECT Name FROM sys.procedures WHERE name LIKE '%StoredProcName%'
To search stored procedures using MS-SQL you can use the snippet below.
SELECT Name FROM sys.procedures WHERE name LIKE '%StoredProcName%'
To split a delimited string to multiple rows using MSSQL you can use the snippet below.
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
To use select statement in a delete statement using MSSQL you can use the snippet below.
DELETE TableOne FROM TableOne INNER JOIN TableTwo ON TableTwo.Id= TableOne.Id AND ... WHERE ...
To get the size of all tables using MSSQL you can use the snippet below.
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
To use a select statement in a update statement using MSSQL you can use the snippet below.
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
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)
To list all databases in MSSQL you can use the following snippet.
SELECT name FROM master..sysdatabases
EXEC sp_databases