Posts

Search entire SQL Server database and all tables for a specified string

-- ============================================= -- Author:        <Jainul Khan> -- Create date: <11-12-2014> -- Description: < Search entire SQL Server database and all tables for a specified string > -- ============================================= -- [dbo].[sp_SearchFromAllTables] 'Entered' CREATE PROCEDURE sp_SearchFromAllTables ( @SearchStr nvarchar ( 100 ) ) AS BEGIN DECLARE @Results TABLE ( ColumnName nvarchar ( 370 ), ColumnValue nvarchar ( 3630 )) SET NOCOUNT ON DECLARE @TableName nvarchar ( 256 ), @ColumnName nvarchar ( 128 ), @SearchStr2 nvarchar ( 110 ) SET   @TableName = '' SET @SearchStr2 = QUOTENAME ( '%' + @SearchStr + '%' , '''' ) WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN ( QUOTENAME ( TABLE_SCHEMA ) + '.' + QUOTENAME ( TABLE_NAME )) FROM INFORMATION_SCHEMA . TABLES WHERE        TABLE_TYPE = 'BASE TABLE' AND QUOTENAME ( TABLE_SCHEMA ) + '.' + QUOTENAME ( TABLE_NAME ) > @TableName AND OBJECTPROPERTY ( OBJECT_ID (

Read more →

Extended Date Formats in SQL Server

Here are some more date formats that does not come standard in SQL Server as part of the  CONVERT  function. Extended Date Formats Date Format SQL Statement Sample Output YY-MM-DD SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] 99-01-24 YYYY-MM-DD SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] 1999-01-24 MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] 08/99 MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005 YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08 YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]

Read more →

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 →

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 →

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 →

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 →