Miracle Scripts For Ever in SQL Server

                                                   Miracle Scripts

To Simple script to backup all SQL Server databases

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files 

DECLARE @fileName VARCHAR(256) -- filename for backup 

DECLARE @fileDate VARCHAR(20) -- used for file name 
SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR

SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @name   WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name   END  

CLOSE db_cursor  
DEALLOCATE db_cursor


To get the user Session count in SQL Server:

SELECT  dec.client_net_address ,

        des.program_name ,

        des.host_name ,

      --des.login_name ,

        COUNT(dec.session_id) AS connection_count

FROM    sys.dm_exec_sessions AS des

        INNER JOIN sys.dm_exec_connections AS dec

                       ON des.session_id = dec.session_id

GROUP BY dec.client_net_address ,

         des.program_name ,

         des.host_name

      -- des.login_name

-- HAVING COUNT(dec.session_id) > 1

ORDER BY des.program_name,

         dec.client_net_address ;

To get the All Stored procedures name from table name used:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'







To get the All Stored procedures name from column name used:

----Method 1

SELECT obj.Name SPName, sc.TEXT SPText

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'columnname' + '%'

AND TYPE = 'P'-------------for stored procedures

--AND TYPE = 'TR'------------for triggers




----Method 2

SELECT

Object_Name(Object_Id)

FROM

sys.sql_modules

WHERE

definition LIKE '%columnname%'

AND definition LIKE '% Procedure %'



To find the references of the Object name:

----Method 1

SELECT DISTINCT o.name AS ObjectName,

CASE o.xtype

WHEN 'C' THEN 'CHECK constraint'

WHEN 'D' THEN 'Default or DEFAULT constraint'

WHEN 'F' THEN 'FOREIGN KEY constraint'

WHEN 'FN' THEN 'Scalar function'

WHEN 'IF' THEN 'In-lined table-function'

WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'

WHEN 'L' THEN 'Log'

WHEN 'P' THEN 'Stored procedure'

WHEN 'R' THEN 'Rule'

WHEN 'RF' THEN 'Replication filter stored procedure'

WHEN 'S' THEN 'System table'

WHEN 'TF' THEN 'Table function'

WHEN 'TR' THEN 'Trigger'

WHEN 'U' THEN 'User table'

WHEN 'V' THEN 'View'

WHEN 'X' THEN 'Extended stored procedure'

ELSE o.xtype

END AS ObjectType,

ISNULL( p.Name, 'Location') AS Location

FROM syscomments c

INNER JOIN sysobjects o ON c.id=o.id

LEFT JOIN sysobjects p ON o.Parent_obj=p.id

WHERE c.text LIKE '%objectname%'----------------like tablename,columnname etc

ORDER BY Location, ObjectName




----Method 2

sp_depends 'dbo.objectname'




To get the Tablename from column value in a table:

EXEC SearchAllTables 'search string''

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (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(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
      ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

To remove the HTML tag from the string using this function:

ALTER FUNCTION [dbo].[udf_StripHTML]

(
@HTMLText varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Start  int
DECLARE @End    int
DECLARE @Length int

-- Replace the HTML entity & with the '&' character (this needs to be done first, as
-- '&' might be double encoded as '&')
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &lt; with the '<' character
SET @Start = CHARINDEX('&lt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '<')
SET @Start = CHARINDEX('&lt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &gt; with the '>' character
SET @Start = CHARINDEX('&gt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '>')
SET @Start = CHARINDEX('&gt;', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &amp; with the '&' character
SET @Start = CHARINDEX('&amp;amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX('&amp;amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity &nbsp; with the ' ' character
SET @Start = CHARINDEX('&nbsp;', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, ' ')
SET @Start = CHARINDEX('&nbsp;', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Replace any <br> tags with a newline
SET @Start = CHARINDEX('<br>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX('<br>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace any <br/> tags with a newline
SET @Start = CHARINDEX('<br/>', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX('<br/>', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace any <br /> tags with a newline
SET @Start = CHARINDEX('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Remove anything between <whatever> tags
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('<', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
SET @Length = (@End - @Start) + 1
END

RETURN LTRIM(RTRIM(@HTMLText))

END

To remove the <img HTML tag from the string using this function:
Alter FUNCTION [dbo].[udf_StripHTMLIMAGE]

(
@HTMLText varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @Start  int
DECLARE @End    int
DECLARE @Length int

-- Remove anything between <whatever> tags
SET @Start = CHARINDEX('<img', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<img', @HTMLText))
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX('<img', @HTMLText)
SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<img', @HTMLText))
SET @Length = (@End - @Start) + 1
END


RETURN LTRIM(RTRIM(@HTMLText))

END





To Find Nth Highest salary from employee:







Select * from ( select  Row_number() over (  order by salary desc ) as Row_INDEX ,
 * From employees ) as Temp where Row_INDEX = 2




To See tablename and its Row count:




SELECT 

    [TableName] = so.name, 

    [RowCount] = MAX(si.rows) 

FROM 

    sysobjects so, 

    sysindexes si 

WHERE 

    so.xtype = 'U' 

    AND 

    si.id = OBJECT_ID(so.name) 

GROUP BY 

    so.name 

    --having MAX(si.rows)=0

--ORDER BY 

--    so.name

--compute sum(MAX(si.rows))







To See identity tablename from the database:




select distinct TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')=1
order by TABLE_NAME

Find all objects in Server Owned by a Login:

In response to a request for a script to show all of the objects on a server onwed by a Login. I didn't know of any tool built-in to do this, so I wrote the following proc (SQL Server 2005 only).
Note the following featues also:

1. No cursors or other Loops (but total number of databases is limitedt to 255).
2. If the DB User for a Login is the explicit Owner for an object, then that will be reported.
3. If there is no explicit owner for an object, then the Login of the DB User that owns the schema containing the object will be returned as the owner.
4. If there is no explicit owner for an object and the object is contained in the [dbo] schema, then the Login that owns the database will be returned as the object's owner (this is the correct method of assesing object ownership in SQL server 2005).

If the Dynamic SQL concerns you, then notice that the Login name filter is only applied at the end. This means that you could also take the PRINT output and turn it into a static View. If you leave off the Login name WHERE clause, then this view returns every SQL object in your server with it's proper owner. This static View cna be preserved and reused as-is so long as your database configuration does not change.
You could write a SQL Server 2000 version of this also, but it would be significantly different, because of the large changes in the Schema/Owner security model, though probably simpler.

CREATE Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
        Display all objects in all DBs owned by the Login.

2008-07-06 RBarryYoung  Created.
2008-08-28 RBarryYoung    Added corrections for DBs with different Collations
            (note that ReportingDBs have different Collations)

Test:
 EXEC spLogin_OwnedObjects 'sa'
*/
    declare @sql varchar(MAX), @DB_Objects varchar(MAX)
    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User]
    , o.name COLLATE DATABASE_DEFAULT as [name]
    , o.object_id
    , o.principal_id
    , o.schema_id
    , o.parent_object_id
    , o.type COLLATE DATABASE_DEFAULT as [type]
    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]
    , o.create_date
    , o.modify_date
    , o.is_ms_shipped
    , o.is_published
    , o.is_schema_published
     From %D%.sys.objects o
      Join %D%.sys.database_principals u
        ON Coalesce(o.principal_id
             , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))
            = U.principal_id
      Left Join %D%.sys.server_principals L on L.sid = u.sid
'

    Select @sql = 'SELECT * FROM
    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] = 'master'

    Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] != 'master'

    Select @sql = @sql + ') oo  Where Login = ''' + @Login + ''''

    print @sql
    EXEC (@sql)

----------To find specific data in table include columns and table and value--------------

DECLARE @tableName varchar(250)
DECLARE @numericColumns varchar(max)
DECLARE @searchValue int
DECLARE @sql varchar(max)

SELECT @tableName='EmployeeRecord' --modify this to the target table's name
SELECT @searchValue=7499 --modify this to specify the value you're searching for.

SELECT @numericColumns = ISNULL(@numericColumns, '') + ' OR ' + QUOTENAME(COLUMN_NAME) + '=' +

CAST(@searchValue AS varchar(10))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tableName
    AND DATA_TYPE IN ('int') --you can modify this to look at other columns besides INT
   
SELECT @numericColumns = STUFF(@numericColumns, 1, 4, '')

SELECT @sql = 'SELECT * FROM ' + QUOTENAME(@tableName) + ' WHERE ' + @numericColumns

SELECT @sql AS sqlStatement
EXEC(@sql)


Output:




---------------split the string whatever your requirement----------------


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringSplitter]') AND type in

(N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[StringSplitter]

GO

CREATE FUNCTION [dbo].[StringSplitter](@str varchar(8000), @delimiter char(1))       



RETURNS TABLE AS

RETURN

    SELECT

            ItemNumber  = ROW_NUMBER() OVER(ORDER BY(SELECT 1))

            ,Items = Split.a.value('.', 'VARCHAR(100)')

    FROM

        (

            SELECT   

                CAST('<X>' + REPLACE(@str,  @delimiter , '</X><X>') + '</X>' AS XML) AS

Splitdata 

        ) X 

    CROSS APPLY Splitdata.nodes('/X') Split(a) 


--output:

--select * from dbo.[StringSplitter]('ashok string2 string3,string4','3')-it will split 2 segment one is

before of value 3 and second is after 3

--so output would be itemno  items
                      1    ashok string2 string
                      2    ,string4



---To Find Privileged Users in a MS SQL Server--


select a.name as LoginName, a.type_desc as LoginType, a.default_database_name as 

DefaultDBName, case when b.sysadmin = 1 THEN 'sysadmin' when b.securityadmin=1 THEN 

'securityadmin' when b.serveradmin=1 THEN 'serveradmin' when b.setupadmin=1 THEN 

'setupadmin' 

when b.processadmin=1 THEN 'processadmin' when b.diskadmin=1 THEN 'diskadmin' when 

b.dbcreator=1 THEN 'dbcreator' when b.bulkadmin=1 THEN 'bulkadmin' else 'Public' end as 

ServerRole from sys.server_principals a join master..syslogins b on a.sid=b.sid where a.type 

<> 'R' and a.name not like '##%'

--------To see the Local Tcp Port--------

select distinct local_net_address, local_tcp_port
from sys.dm_exec_connections
where net_transport = 'TCP'

------------------------------------------------------

Linked Server Test:

--EXEC sp_addlinkedserver   'SEATTLESales','SQL Server' ---------To add the linked server list

select * from sys.servers---------To see the linked server list

exec sp_linkedservers---------To see the linked server list

1)exec sp_testlinkedserver linkserverame----------To test the linked server connectivity


2)sp_tables_ex linkservername----------To test the linked server connectivity


3)SELECT name FROM [SHAREPOINTDEV2].master.sys.databases-------To see the linked server databases


4)----------To test the linked server connectivity using script-------------
declare @srvr nvarchar(128), @retval int;
set @srvr = 'linkedservername';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );


5)Other Method:
-- CREATE PROC procPingLinkedServer  @LinkedServer sysname
--/*  RETURNS
--        1 --> Connection success
--        0 --> Connection failure
--*/
--AS
--BEGIN
--DECLARE @Command nvarchar(1048)
--SET NOCOUNT ON;
--CREATE TABLE #PingTest (  CmdResultBuffer varchar(128));
--SET @Command = 'ping '+ @LinkedServer
--PRINT @Command
--INSERT #PingTest
--    EXEC master..xp_cmdshell   @Command;
--
--IF EXISTS ( SELECT 1 FROM #PingTest WHERE CmdResultBuffer LIKE '%TTL%' )
----    RETURN 1;
----ELSE
----    RETURN 0;
--
--
--drop table #PingTest
--END
--GO
--
---- Test linked server connectivity
--DECLARE @Connection int
--EXEC @Connection = procPingLinkedServer 'SHAREPOINTDEV2'
--SELECT ConnectionStatus = @Connection



6)---check linked server logins-------


SELECT s.name,data_source,provider,
       is_remote_login_enabled,is_rpc_out_enabled,
       is_data_access_enabled,uses_self_credential,
       remote_name
  FROM sys.servers s INNER JOIN
       sys.linked_logins ll on s.server_id=ll.server_id
 WHERE s.server_id != 0

7)--------------Check linked server login and local------------
SELECT local.name AS LocalLogins, linked.name AS LinkedLogins
FROM master.sys.server_principals AS local
LEFT JOIN [sbsdev].master.sys.server_principals AS linked
    ON local.name = linked.name ;

8)Query to Return range of records:

Problem:

How to get top 10 records then records from 11-20 then 21-30

The scenario is like, if the index from front end is given, then it should return records accordingly,

Index   rows

1              1-10

2              11-20

3              21-30

Solution:

declare @index int

set @index=2

;with cte

as

(select *, ROW_NUMBER() over(order by BusinessEntityId )as row_num fromHumanResources.Employee

)

select * from cte where row_num>=(@index*10-9) and row_num<=(@index*10)

In the above solution, I have applied this on table ‘HumanResources.Employee’ of AdventureWorks2008 database. It returns records for index 2 thus returns records from 11-20

We can make this solution generic,

select *, ROW_NUMBER() over(order by [Column_NameForOrderBy] )as row_num from[Table_Name] 




T-SQL Split Function : Split Field By a Delimiter

Ref:

http://www.rad.pasfu.com/index.php?/archives/56-T-SQL-Split-Function-Split-Field-By-a-Delimiter.html#extended

Suppose that our Primary Data is like this image and delimited by single slash ( / )

I created a Scalar Value T-SQL Function to create the string to be used in select statement, so the function will return a string which should be used in a select statement and the function itself doesn't split data. result of this function should be used in a dynamic t-sql to return result as expected.

This is script of the function:

-- ================================================
-- This Function will generate script to be used
-- in a select statement and fetch every delimited
-- as a column
-- Use it in this way: (Example)
--  declare @sql varchar(max)
-- set @sql='select '+dbo.SplitFieldByDelimiter('ZoneName','/',8)
-- +' from [TVNZ_NDS].[Staging].[tmpImpressionExtract]'
-- 
-- exec (@sql)
 -- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Reza Rad
-- Version:     1.0
-- Create date: 21/02/2012
-- Description:    To Split a Delimited field by a Delimiter
-- =============================================
CREATE FUNCTION dbo.SplitFieldByDelimiter 
(
    @fieldname varchar(max)
    ,@delimiter varchar(max)
    ,@delimiter_count int
)
RETURNS varchar(max)
AS
BEGIN
    -- variable declaration
    declare @first_index varchar(max)
    ,@next_index varchar(max)
    ,@sql_statement varchar(max)
    ,@term_statement varchar(max)
    ,@length varchar(max)
    ,@iterator int

    -- initialization
    set @next_index=-1
    set @iterator=1
    set @sql_statement=' '

    -- code
    while(@iterator<=@delimiter_count)
    begin
        set @first_index=@next_index+'+1'
        set @next_index='CHARINDEX('''+@delimiter+''','+@fieldname+','+@first_index+')'
        if (@iterator=@delimiter_count)
        begin
        set @next_index='LEN('+@fieldname+')+1'
        end
        set @length=@next_index+'-('+@first_index+')'
        set @term_statement='SubString('+@fieldname+','+@first_index+','+@length+')'
        set @term_statement='case when '+@next_index+'>0 then '+@term_statement+' else ''nothing'' end as Column'+CONVERT(varchar(max),@iterator) 
        if (@iterator=1) 
        begin
            set @sql_statement=@term_statement
        end
        else
        begin
            set @sql_statement=@sql_statement+', '+@term_statement
        end

        set @iterator=@iterator+1
    end


    -- Return the result of the function
    RETURN @sql_statement

END
GO

and This is they way we use it :

declare @sql varchar(max)
set @sql='select top 1000 '+dbo.SplitFieldByDelimiter('DelimitedText','/',8)
+' from [TmpDataTable]'

exec (@sql)

So result will be like this :

In this version of Split function you need to enter number of occurrence of delimiter as function parameters, but I will solve it soon and publish version 2 without need for occurrence.

T-SQL Script: Find Keyword in whole Database

Ref:

http://www.rad.pasfu.com/index.php?/archives/65-T-SQL-Script-Find-Keyword-in-whole-Database.html#extended

There are some times that you need to find all occurrence (or some of them) of a keyword in all columns/tables in a database, This is a usual scenario in data profiling.

I wrote script below to fulfill this requirement, this script will search for specific keyword in all columns and tables of a database (SQL Server database) and returns list of Schemas, Tables, Columns and occurrence of keyword as a result

declare @keyword nvarchar(max)

set @keyword='David'

declare @schema varchar(max)

declare @table varchar(max)

declare @column varchar(max)

declare @sqlstatement nvarchar(max)

declare @totalrecords int

declare @counter int

declare @occurrence int

declare @objects table

(

SchemaName varchar(max),

TableName varchar(max),

ColumnName varchar(max),

IsProcessed bit,

Occurrence int)

insert into @objects(SchemaName,TableName,ColumnName,IsProcessed)

select sch.name,tab.name,col.name,0

from sys.columns col

inner join sys.tables tab

on col.object_id=tab.object_id

inner join sys.schemas sch

on tab.schema_id=sch.schema_id

where col.system_type_id not in (

34,--image

241--xml

)

order by sch.name,tab.name,col.name

select @totalrecords=count(*) from @objects

set @counter=0

while (@counter<=@totalrecords)

begin

select top 1 @schema=SchemaName,@table=TableName,@column=ColumnName

from @objects

where isprocessed=0

order by SchemaName,TableName,ColumnName

set @sqlstatement='select @occurrence=count(*) from ['+@schema+'].['+@table+'] where ['+@column+'] like ''%'+@keyword+'%'''

exec sp_executesql @query=@sqlstatement,@params=N'@occurrence int output',@occurrence=@occurrence output

update @objects

set IsProcessed=1,

Occurrence=@occurrence

where SchemaName=@schema and TableName=@table and ColumnName=@column

set @counter=@counter+1

end

select SchemaName,TableName,ColumnName,Occurrence from @objects

This script took 5 minutes to run on a database with 120 tables (total number of 1400 columns)

This is sample result of script:

To run this script you just need to open a query window in SSMS, select database, change value assigned to @keyword variable and run the query.

To Replace Single space with Double Space:

declare @text varchar(max)

set @text=' St    Charles'

while charindex('  ',@text)>0

 set @text=replace(@text,'  ',' ')

select @text

O/P:


 St Charles