Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 20 November 2012

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

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


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