Posts

Find and Replace Values in All Tables and All Text Columns using SQL Server

DECLARE @stringToFind VARCHAR ( 100 ) DECLARE @stringToReplace VARCHAR ( 100 ) DECLARE @schema sysname DECLARE @table sysname DECLARE @count INT DECLARE @sqlCommand VARCHAR ( 8000 ) DECLARE @where VARCHAR ( 8000 ) DECLARE @columnName sysname DECLARE @object_id INT SET @stringToFind = 'Smith' SET @stringToReplace = 'Jones' DECLARE TAB_CURSOR CURSOR FOR SELECT    B . NAME      AS SCHEMANAME ,          A . NAME      AS TABLENAME ,          A . OBJECT_ID FROM sys . objects A INNER JOIN sys . schemas B ON A . SCHEMA_ID = B . SCHEMA_ID WHERE TYPE = 'U' ORDER BY 1 OPEN TAB_CURSOR FETCH NEXT FROM TAB_CURSOR INTO @schema ,      @table ,      @object_id

Read more →
0

Find Stored Procedure Containing by Table Name

SELECT DISTINCT t . name [TableName] , p . name [ProcedureName] FROM sys . objects t LEFT JOIN sys . sql_dependencies d ON     d . referenced_major_id = t . object_id LEFT JOIN sys . objects p ON     p . object_id = d . object_id WHERE t . type = 'u' and t . name like '%tbl_Order%'

Read more →
0

Find Tables in a Database Who Have a Filed With Same Name

SELECT SCHEMA_NAME ( schema_id ) + '.' + t . name AS 'Table Name' FROM sys . tables t INNER JOIN sys . columns c ON c . object_id = t . object_id WHERE c . name like '%OrderID%' ORDER BY 'Table Name'

Read more →
0

Find and Delete Duplicate Rows From a Table in SQL Server

WITH CTE AS ( SELECT *,    RN = ROW_NUMBER () OVER ( PARTITION BY OrderID ORDER BY OrderID ) FROM tbl_Order   ) SELECT * FROM CTE WHERE RN > 1 DELETE FROM CTE WHERE RN > 1

Read more →
0