Run Large SQL Script Files sql server 2005

Sql server 2005 management studio do not support execution of sql files larger than 50 MB .
but if we have script files larger than 50MB we can execute them by using SQLCMD tool like
that

SQLCMD -S MyServer -d MyDatabase -E -i C:\Scripts\MyScriptFile.sql

SQL Server 2005: Missing Management Studio from Installation

Ever wondered why is there no SQL server management studio after a successful installation of your SQL Server 2005? This is because by default the SQL 2005 installer does not install this tool. To install the studio just run "SqlRun_Tools.msi" from your setup disk. On enterprise edition, "SqlRun_Tools.msi" is located on D:\Tools\Setup.




Location from CD:


After running the SqlRun_Tools.msi, new items will be displayed on Microsoft SQL Server 2005 menu

collation error occures in sql2k5

If a collation error occurres then can be resolved by mentioning the collation in query such like that

SELECT Column1, Column2


FROM MyTable M

WHERE EXISTS (SELECT * FROM ThisTable T WHERE M.Column1 = T.Column1

COLLATE Latin1_General_CI_AS)

Unable to connect Database Erwin

Some time we face problems while connecting Erwin to Sql server ,
to resolve the issue

just download ntwdblib.dll and place it into system32 folder.

How to find Sql server version

Following query can be used to find which sql server 2005 version is installed .

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Date Picker in Excel Sheet Column

You can display a calendar control on a user form if you wish. This action may be triggered by right-clicking a cell (my preference) or by selecting it (your request). The code below is activated by selecting a cell in the range A1:A10.




First, you probably need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.





1) ALT + F11 to open the VBA Editor

2) Insert...User Form to create a blank user form

3) Open the Tools...Additional Controls menu item, then scroll down until you find the Microsoft Date and Timer Picker. Make sure to check its box.

4) Back in the user form, click the new DT Picker control on the Forms toolbar, then draw the control on the form

5) Right-click the user form, then choose "View code". Insert the following subs (which assume that your form is called UserForm1 and it contains a calendar control named DTPicker1.



Private Sub DTPicker1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

ActiveCell.Value = UserForm1.DTPicker1.Value

End Sub



Private Sub UserForm_Initialize()

UserForm1.DTPicker1.Value = Date

End Sub



6) ALT + F11 to return to the Excel Worksheet, then right-click the sheet tab and choose "View code" from the resulting pop-up. Paste the following code there:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, [A1:A10]) Is Nothing Then Exit Sub



UserForm1.Show

End Sub



7) ALT + F11 to return to the worksheet

Running Total Query Sql

We can get running total against fields by using  a self join like

CREATE TABLE #Table


(

Id int,

Name varchar(50),

Length int

)



INSERT INTO #Table (Id, Name, Length)

SELECT 1, 'Field1', 23 UNION ALL

SELECT 2, 'Field2', 34 UNION ALL

SELECT 3, 'Field3', 21 UNION ALL

SELECT 4, 'Field4', 10 UNION ALL

SELECT 5, 'Field5', 5





SELECT F1.Id,f1.Name,f1.Length,ISNULL(SUM(F2.Length),0) AS RunTotal FROM #Table F1

LEFT JOIN #Table F2 ON f1.Id - 1 >=f2.Id

GROUP BY F1.Id,F1.Name,f1.Length

Using Cross Apply to Use Like Operator on delimitted string values

Declare @vTable TABLE(id INT, NAME VARCHAR(100))
INSERT INTO @vTable
    SELECT 1,'faheem' UNION ALL
    SELECT 2,'ahmad' UNION ALL
    SELECT 3,'aslam UNION ALL
    SELECT 4,'bilal' UNION ALL
    SELECT 4,'khizar'
DECLARE @vParam VARCHAR(100)
-- To check the values with LIKE operator. These are comma separated.
SET @vParam = 'fah,asl'
-- Used CROSS APPLY to accomplish the task...
SELECT * FROM @vTable
CROSS APPLY (SELECT [item] FROM dbo.fnSplit(@vParam,',')) b
WHERE NAME LIKE '%' + b.[item] + '%'

fnSplit can be created like

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

Find who is connected to database in a current instance

in Sqlserver 2005 system stored procedure "sp_who" can be used to get information about current users and processes in an instance of the Microsoft SQL Server Database Engine. The information returned can be filtered to return only those processes that are not idle.

Using Exists in When clause

SELECT SEQ.Name AS SeqNumber,


Seq.Id,

CASE WHEN EXISTS(SELECT * FROM dbo.tblSYSASEQData WHERE IsDefault='true' AND SeqId=SEQ.Id) THEN

(SELECT Data FROM dbo.tblSYSASEQData WHERE IsDefault='true' AND SeqId=SEQ.Id) ELSE (SELECT TOP 1 Data FROM dbo.tblSYSASEQData WHERE SeqId=SEQ.Id ORDER BY SEQid) end AS SEQData

FROM dbo.tblSYSASEQAssociation SEQA

INNER JOIN dbo.tblSYSASEQ SEQ ON SEqA.SeqAssociationId=SEQ.SeqAssociationId

INNER JOIN dbo.tblSYSAUser U ON U.Id=SEQA.UserId

WHERE U.Id=120

Query to Find Missing values in a sequence

Suppose we have a table named sequence

we can write query to find missing(at least first missing) value/values in a sequence of integers like that

Suppose we have a table named sequence
SELECT SEQ1.Id+1 FROM #sequence SEQ1 LEFT OUTER JOIN #sequence SEQ2 ON SEQ1.id+1=SEQ2.id
WHERE SEQ2.id IS NULL

TallyCTE for CSV Values

DECLARE @MfrCodes VARCHAR(8000)


SELECT @MfrCodes = '1DEF,ABS,ACI,ACL,AEA' ;



SELECT @MfrCodes = ',' + @MfrCodes + ',' ;









WITH cteTally

AS (

SELECT ROW_NUMBER() OVER ( ORDER BY o.id ) AS N

FROM master.sys.sysobjects o

CROSS JOIN master.sys.sysobjects o2

),







cteSplit

AS

(

SELECT SUBSTRING(@MfrCodes, N + 1,

CHARINDEX(',', @MfrCodes, N + 1) - N - 1) AS VALUE,N,CHARINDEX(',', @MfrCodes, N + 1) - N - 1 AS VALUE2

FROM cteTally

WHERE N < LEN(@MfrCodes)

AND SUBSTRING(@MfrCodes, N, 1) = ','

)

SELECT *,@MfrCodes

FROM cteSplit

Recursive CTE to Generate Sequnce Numbers upto any limit

WITH CTE (num) AS (

SELECT 1

UNION ALL

SELECT num+1

FROM CTE

WHERE num<90000

)

SELECT DISTINCT num FROM CTE c1

OPTION (MAXRECURSION 0)

Find a specific column in all database tables

following query can be used to find a specific column in all database tables

select table_name from information_schema.columns where column_name = 'Inactive'

Delete A service

Some times a service installer corrupts and service is not uninstalling from the system ,
we can use
sc delete SERVICENAME
command to delete such a service

Check your query performance.

Set NoCount On


Declare @cpu_ int

Declare @lreads_ int

Declare @eMsec_ int





Select @cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

From sys.dm_exec_requests

Where session_id = @@spid



--======

-- Your Test Code Goes Here

--======

Select cpu_time-@cpu_ as CpuMs

, logical_reads- @lreads_ as LogRds

, total_elapsed_time - @eMsec_ as Elapsed

From sys.dm_exec_requests

Where session_id = @@spid

Query to Fill Table with Dates

Here is a simple query to insert dates from 2000-07-03 00:00:00.000 to 2020-06-27 00:00:00.000

with DateCTE as (


SELECT dateadd( dd,-3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N

FROM (

SELECT TOP 7300

row_number() OVER (ORDER BY sc1.id) AS RW

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2

) X

)

--the SQL2008 DATE datatype, instead of datetime

SELECT convert(DATETIME,DateCTE.N)

from DateCTE

Cross Tab Queries

Often we need to use cross tab queries
we can use Pivot function of sqlserver 2005.
consider an example
say, we have following sort of table
DECLARE @Test TABLE (
SalesPart char(11),
WCDate datetime,
[Delivery Date] datetime,
Qty int
)

INSERT INTO @Test
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'21-MAR-10 00:00:00.000',113), 4147 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'22-MAR-10 00:00:00.000',113), 1963 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'23-MAR-10 00:00:00.000',113), 2639 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'24-MAR-10 00:00:00.000',113), 4498 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'25-MAR-10 00:00:00.000',113), 3523 UNION ALL
SELECT '883/575/013', CONVERT(datetime,'21/03/2010',103), CONVERT(datetime,'27-MAR-10 00:00:00.000',113), 6721
We can write a Cross Tab query for the above data
like
SELECT SalesPart, WCDate, Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday
FROM (
SELECT SalesPart, WCDate, datename(weekday ,[Delivery Date]) AS weekday, Qty
FROM @Test
) AS T
PIVOT (SUM(Qty) FOR weekday in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Saturday])) AS Pvt
Another Solution could be
SELECT T.SalesPart,


T.WCDate,

SUM(CASE WHEN CA.week_day = 'Sunday' THEN T.Qty ELSE 0 END) AS Sunday,

SUM(CASE WHEN CA.week_day = 'Monday' THEN T.Qty ELSE 0 END) AS Monday,

SUM(CASE WHEN CA.week_day = 'Tuesday' THEN T.Qty ELSE 0 END) AS Tuesday,

SUM(CASE WHEN CA.week_day = 'Wednesday' THEN T.Qty ELSE 0 END) AS Wednesday,

SUM(CASE WHEN CA.week_day = 'Thursday' THEN T.Qty ELSE 0 END) AS Thursday,

SUM(CASE WHEN CA.week_day = 'Friday' THEN T.Qty ELSE 0 END) AS Friday,

SUM(CASE WHEN CA.week_day = 'Saturday' THEN T.Qty ELSE 0 END) AS Saturday

FROM @Test T

CROSS APPLY (SELECT DATENAME(WEEKDAY, T.[Delivery Date])) CA (week_day)

GROUP BY

T.SalesPart,

T.WCDate;

Find a Sepecific parameter type in all storedprocedures

some times we may require to find all the stored procedures where a specific dataype is used as a parameter or where a specific parameter name is used .
we can search those strored procedures using "sys.all_parameters","systypes","sys.objects"
say we want to know in which stored procedures data type smallint is used , we can do so by using the follwoing SQL

select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name, t.name param_type
from sys.all_parameters p
join systypes t on t.xusertype = p.user_type_id
join sys.objects o on o.object_id = p.object_id
where o.type = 'P' -- type procedure
and t.name = 'smallint' -- data type
and o.is_ms_shipped = 0 -- user created
order by
schema_name(o.schema_id),
o.name;
AND
To search for a specific parameter name we can edit the above query as
select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name
from sys.all_parameters p

join sys.objects o on o.object_id = p.object_id
where o.type = 'P' -- type procedure
and o.is_ms_shipped = 0 -- user created
and p.name='@OperatorId'
order by
schema_name(o.schema_id),
o.name;

Update record in a Database on the basis of Other Database

Some times we need to sync our tables with other tables which may have some other database .
so we can use update feature like that
update person
set name = B.personname,
age=B.age

from DB1.dbo.person as A, DB2.dbo.person2 as B
WHERE A.id = B.personid

Find the Last Occurrence of a character in a string in SQL

Some times we need to get the last occurrence of a character in any string , charindex returns with the index of first occurrence of a character but we can still use it by playing a small trick
consider an example
suppose we have a string 'C:\ProgramFiles\Test\Test.xml'
and we want to get file name i.e Test.xml
we can use Reverse method to get the occurrence of '\' character , like
Declare @FileName varchar(100)
Set @FileName = 'C:\ProgramFiles\Test\Test.xml'

Select Substring(@FileName,
LEN(@FileName ) -
Charindex('\',Reverse(@FileName ))+2,
LEN(@FileName ))



OR
we can use RIGHT Function like
DECLARE @FileName VARCHAR(255)

SET @FileName = 'C:\ProgramFiles\Test\Test.xml'

SELECT RIGHT( @FileName , CHARINDEX( '\', REVERSE( @FileName ) + '\' ) - 1 )

Convert Minutes, Seconds to more readable Form in SQL

when we use datediff function in SQL to determine how log something is running, we have to make the choice how to represent it. If we choose minutes, SQL will round the datediff time. If we choose seconds, we'll have to determine how many minutes + some seconds this was For Example 5315027 seconds means 12:23:47 i.e 12 hours 23 minutes and 47 seconds which is more understandable than 5315027
we can write query as
select CONVERT(varchar(8),DATEADD(second,datediff(ss,GetDate(),Convert(Datetime,'07-07-10')) ,0),114)
or we can create a UDF like that
create function [dbo].[SecondsToText](@seconds int) returns varchar(8)
as
    begin
        return (select CONVERT(varchar(8),DATEADD(second,@seconds,0),114))
    end
go
Same can be Used with Minutes but of course with Slight change in parametes
select CONVERT(varchar(8),DATEADD(mi,datediff(mi,GetDate(),Convert(Datetime,'07-07-10')) ,0),114)
or a Function like that
create function [dbo].[MinutesToText](@minutes int) returns varchar(8)
as
    begin
        return (select CONVERT(varchar(8),DATEADD(mi,@minutes,0),114))
    end
go

Custom Exception in SQL Server

We can use rich functionality of Try Catch block to catch an exception
and we can use ERROR_NUMBER()to get specific errro number.
Custome exception can be thrown by using RAISERROR

Example

BEGIN TRY
exec [dbo].[spSelectRecordsTest]
END TRY

BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
IF( ERROR_NUMBER()=208)
RAISERROR('INVALLID OBECT NAME ',16,1)
ELSE
RAISERROR('MY CUSTOME EXCEPTION',16,1)
END CATCH;

Asp.net Numeric Text Box

WE can use Regular Expression vallidator to allow a text box to accept only numeric values by setting the property as
ValidationExpression="(^([0-9]*|\d*\d{1}?\d*)$)"

How to use Database mail feature in SQL Server 2000

We are all talking about the latest versions and features available in SQL Server but most of us are still working on older, SQL Server 2000 servers. It might take a few more years before all of the existing SQL Server 2000 applications are upgraded to newer versions of SQL Server or to other database products. This means we are still stuck with using the old, outdated features available in SQL Server 2000.
One such feature is SQL Mail. I thought that in order to configure email notifications it would be better and easier to use database mail (introduced in SQL Server 2005) rather than SQL Mail where I can. In this article, I will run you through an easier way of using database mail in SQL Server 2000 itself. That is, in your SQL Server 2000, you would be in a position to use database mail instead of SQL Mail for sending job failure notification. This is my procedure:
1) As a first step, you need to configure database mail on your SQL Server 2005 instance if you have not already configured. I haven't covered this in this article, but you can find more information from numerous articles on the internet. You could also go to SQLServer Books Online and read about the wizard. If you are new to SQL Server, don't panic, this takes around 5 to10 minutes to configure.
2) Once database mail is configured or if it is already configured in your SQL Server 2005 server, create a login 'test' on this 2005 instance and make sure you make this login a member of the db_owner role in the msdb database.
3) Once this is done, go to your SQL Server 2000 instance with Query Analyzer and run the sample script below. This will create a linked server on your SQL Server 2000 instance that will help you connect to your SQL Server 2005 instance where you have configured database mail
Please enter the appropriate details relevant to your SQL Server 2005 in the @server and @datasrc parameters.EXEC sp_addlinkedserver @server='SQL_Server_2005 ', @srvproduct='', @provider='SQLOLEDB', @datasrc='SQL Server 2005 instance name'
4) Now Go to SQL Server 2000 instance-> security-> Linked Servers and you will see the new linked server you created. Right click on this new linked server and go to the properties->security tab
Please enter credentials as shown in screenshot, refer to step (2) above where you created a login in SQL Server 2005 (please use the same login details).
And on 'Server Options' tab, we have to enable RPC Out

5) Go to your SQL Server 2000 server and create a test job. Just make sure that this job will fail (eg. Include script for taking backup of non-existent database on your server) and add a job failure notification step on failure of first step. A sample script of the job failure notification step belowexec SQL_Server_2005.msdb.dbo.sp_send_dbmail @profile_name = 'SampleSQLServer2005dbmailProfile', @recipients='yourname@email.com', --@copy_recipients = 'yourgroup@email.com', @subject='Job test_db_mail failed on SQL Server 2000 server', @importance='HIGH', @body ='Job test_db_mail failed on SQL Server 2000 server'
We are referencing the linked server 'SQL_Server_2005' (please refer step 3) that we created on SQL Server 2000. Please enter the appropriate details in @profile_name (this is the database profile you configured in SQL Server 2005) and @recipients parameters. Also, please refer to the screenshot below for your reference.

6) Execute this test job on SQL Server 2000 and in a few minutes you will get a notification to your email address
7) If you did not get the email notification, just make sure that the SQL Server 2005 instance where you are connecting to using linked server is running fine. You could try some test queries from SQL Server 2000 Query analyser to retrieve data from SQL Server 2005.
Example: Select * from openquery(SQL Server 2005 linked server name ,'select * from msdb.dbo.sysjobs')
If the linked server set up is fine, above query should return results.
Conclusion
I always found configuring email notifications a pain in SQL Server 2000. I am sure many of you would have faced difficulties doing the same on SQL Server 2000. I hope by using above method, you don't have to tweak errors you may face while using SQL mail, instead you could start utilizing database mail of SQL Server 2005 and above. Good luck.

Remove empty lines in text using Visual Studio.

Visual Studio has ability to delete empty lines in replace operation using regular expressions
.1.Click Ctrl-H
2. Tick "Use Regular Expressions"
3. In Find specify ^$\n
4. In Replace box delete everything.
5 Click "Replace All"
All empty lines will be deleted.

Regular expression for empty line consist of
Beginning of line ^
End of line $
Line break \n
Note that normally in Windows an end of line indicated by 2 characters CRLF - Carriage Return (CR, ASCII 13, \r) Line Feed (LF, ASCII 10, \n).

Macro to Convert Public Members to Propeties

//*Public Module Utilities
//Declare these two variables under module
Dim repPattern As String
Dim repPatternReadOnly As String
*//Sub ConvertVariables()' Determine current language by looking at the extension of the current document.Dim doc As Document = DTE.ActiveDocumentIf doc Is Nothing Then Exit SubDim docName As String = doc.Name.ToLower()' Read all the text lines touched by the selection.Dim sel As TextSelection = CType(DTE.ActiveDocument.Selection, TextSelection)Dim ed1 As EditPoint = sel.AnchorPoint.CreateEditPoint()ed1.EndOfLine() : ed1.StartOfLine() : ed1.StartOfLine()Dim ed2 As EditPoint = sel.BottomPoint.CreateEditPoint()ed2.EndOfLine()Dim text As String = ed1.GetText(ed2)' The find and replacement pattern depend on the current language.Dim findPattern As StringIf docName.EndsWith(".vb") ThenfindPattern = "(?[\t ]+)Public\s+(?Shared\s+)?(?ReadOnly\s+)?" _& "(?\w+)\s+As\s+(?\S+)(?.*?)\n"' {0}=property name, {1}=property type, {2}=static keyword, {3} initvalue, ' {4}=CR-LF, {5}=Tab, {6}=indentrepPattern = "{6}Private {2}m_{0} As {1}{3}{4}" _& "{6}Public {2}Property {0}() As {1}{4}" _& "{6}{5}Get{4}" _& "{6}{5}{5}Return m_{0}{4}" _& "{6}{5}End Get{4}" _& "{6}{5}Set(ByVal Value As {1}){4}" _& "{6}{5}{5}m_{0} = Value{4}" _& "{6}{5}End Set{4}" _& "{6}End Property{4}{4}"repPatternReadOnly = "{6}Private {2}ReadOnly m_{0} As {1}{3}{4}" _& "{6}Public ReadOnly {2}Property {0}() As {1}{4}" _& "{6}{5}Get{4}" _& "{6}{5}{5}Return m_{0}{4}" _& "{6}{5}End Get{4}" _& "{6}End Property{4}{4}"ElseIf docName.EndsWith(".cs") Then' Notice the (?.*;) element is needed to ensure that public fields are matched, ' but public properties aren'tfindPattern = "(?[\t ]+)public\s+(?static\s+)?(?readonly\s+)?" _& "(?\S+)\s+(?\w+)(?=.*;)(?.*?)\n"' {0}=property name, {1}=property type, {2}=static keyword, {3} initvalue, ' {4}=CR-LF, {5}=Tab, {6}=indentrepPattern = "{6}private {2}{1} _{0}{3}{4}" _& "{6}public {2}{1} {0}{4}" _& "{6}{{{4}" _& "{6}{5}get {{ return _{0}; }}{4}" _& "{6}{5}set {{ _{0} = value; }}{4}" _& "{6}}}{4}{4}"repPatternReadOnly = "{6}private {2}readonly {1} _{0}{3}{4}" _& "{6}public {2}{1} {0}{4}" _& "{6}{{{4}" _& "{6}{5}get {{ return _{0}; }}{4}" _& "{6}}}{4}{4}"End If' Replace the text. Add a trailing CR-LF but remove it later.Dim replaceText As String = Regex.Replace(text + ControlChars.CrLf, findPattern, _AddressOf ReplaceWithProperty)ed1.ReplaceText(ed2, replaceText.Substring(0, replaceText.Length - 2), 0)End Sub' Private callback function for the Replace methodPrivate Function ReplaceWithProperty(ByVal m As Match) As StringDim pattern As String = repPatternIf m.Groups("readonly").Length > 0 Then pattern = repPatternReadOnlyReturn String.Format(pattern, m.Groups("name").Value, m.Groups("type").Value, _m.Groups("static").Value, m.Groups("init").Value, ControlChars.CrLf, _ControlChars.Tab, m.Groups("indent").Value)
End Function

SQL Function to Accept CSV's and to return table

CREATE FUNCTION [dbo].[ConvertListToColumn] (@list nvarchar(MAX))RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,@nextpos int,@valuelen intSELECT @pos = 0, @nextpos = 1WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

Finding Child tables

We can find child tables of a table from the management studio as well as by the following query
select * from sys.foreign_keysselect object_name(parent_object_id), object_name(referenced_object_id)
from sys.foreign_keys

Finding the reference of tables in other tables and Stored procedures

some times we need to update a table/name and we want to know where this table is reffered in other database objects
so we can use following options to find
Option 1
SELECT DISTINCT so.nameFROM syscomments scINNER JOIN sysobjects so ON sc.id=so.idWHERE sc.TEXT LIKE '%tblsysacommunity%'
Option 2
SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE c.TEXT LIKE '%tblsysacommunity%'

Getting Changes in DatabBase with in a specified time

SELECT name, TYPE, type_desc, create_date, modify_date FROM sys.objects WHERE TYPE IN ('U','V','PK','F','D','P') AND modify_date >= Dateadd(HOUR,08,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME)) ORDER BY modify_date
--U - Table
--V - View
--PK - Primary Key
--F - Foreign Key
--D - Default Constraint
--P - Procedure

Recursive CTE to split CSV

Declare @array varchar(max)Declare @del varchar(1)select @array='5,6,7,8,9'
select @del=',';
WITH rep (item,list) AS ( SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item, SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list
UNION ALL
SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item, SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list FROM rep WHERE LEN(rep.list) > 0 )
INSERT INTO @anyTable
SELECT * FROM rep

joining queries on multiple servers

In Sql server we can create Linked servers to execute/join queries on multiple servers/databases.
we can creae linked server by using Management studio option or by using the script like
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerName', @srvproduct=N'DB', @provider=N'SQLNCLI', @datasrc=N'RemoteServerName', @provstr=N'Data Source=RemoteServerName;Initial Catalog=DB_NAME;Persist Security Info=True;User ID=sa;Password=password', @catalog=N'MY_DB' /* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RemoteServerName',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'collation compatible', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'data access', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'dist', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'pub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'rpc', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'rpc out', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'sub', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'connect timeout', @optvalue=N'0'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'collation name', @optvalue=nullGOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'lazy schema validation', @optvalue=N'false'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'query timeout', @optvalue=N'0'GOEXEC master.dbo.sp_serveroption @server=N'RemoteServerName', @optname=N'use remote collation', @optvalue=N'true'
When we are finished with the creation of Linked Server
we can write any query like
Insert Into RemoteServerName.MY_DB.dbo.MY_TABLE
select columns from current server database table.

Getting Duplicate Records in a table

Declare @temp table(id int,name varchar(30),age int)INSERT INTO @temp values(1,'faheem',12)INSERT INTO @temp values(2,'ahmad',12)INSERT INTO @temp values(1,'faheem',12)INSERT INTO @temp values(3,'faheem',14)
select id,name,age,count(*) from @tempgroup by id,name,agehaving count(*)>1

Cross Apply very good feature in sqlserver 2005

Consider a problem
We have to write a query to return the top 3 orders for each customer in adventure works database.
We can do so by the following 3 ways
1-
SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
CROSS APPLY
(SELECT TOP(3) *
FROM Sales.SalesOrderHeader
WHERE CustomerID = C.CustomerID
ORDER BY TotalDue DESC) AS O
ORDER BY
CustomerID ASC, TotalDue DESC
2-
SELECT CustomerID, SalesOrderID, TotalDueFROM ( SELECT O.CustomerID, O.SalesOrderID, O.TotalDue, ROW_NUMBER = ROW_NUMBER() OVER ( PARTITION BY O.CustomerID ORDER BY O.TotalDue DESC) FROM Sales.SalesOrderHeader AS O ) AS dWHERE d.ROW_NUMBER <= 3
We can change above query for better understanding as

with O AS
(
SELECT ROW_NUMBER()
OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC)
AS rn ,Sales.SalesOrderHeader.* FROM Sales.SalesOrderHeader
)
SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
Inner Join O on C.CustomerID=O.CustomerID
Where O.rn<=3

Find a string value in all database tables

DECLARE @TEXT VARCHAR(500)SET @TEXT = 'Faheem'
DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500), ColumnName VARCHAR(500))INSERT INTO @TABLES(TableName, ColumnName)SELECT O.[NAME], C.[NAME]--SELECT *FROM SYSOBJECTS OJOIN SYSCOLUMNS C ON C.ID = O.IDWHERE O.XTYPE = 'U' AND C.XTYPE NOT IN ( 127 --bigint , 173 --binary , 104 --bit , 61 --datetime , 106 --decimal , 62 --float , 34 --image , 56 --int , 60 --money , 108 --numeric , 59 --real , 58 --smalldatetime , 52 --smallint , 122 --smallmoney , 189 --timestamp , 48 --tinyint , 36 --uniqueidentifier , 165 --varbinary )ORDER BY O.[NAME], C.[NAME]
IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TMPREPORT%')BEGIN DROP TABLE #TMPREPORTENDCREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500), COLUMNNAME VARCHAR(500))
DECLARE @CNTR INT, @POS INT, @TableName VARCHAR(500), @ColumnName VARCHAR(500), @SQL VARCHAR(8000)SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''FROM @TABLES
--SELECT @POS, @CNTR, * FROM @TABLES
WHILE @POS <= @CNTRBEGIN SELECT @TableName = TableName, @ColumnName = ColumnName FROM @TABLES WHERE [ID] = @POS
SELECT @SQL = 'SELECT COUNT(*), ''' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @TableName + ' WHERE CAST(' + @ColumnName + ' AS VARCHAR) LIKE ''%' + @TEXT + '%''' --PRINT @SQL BEGIN TRY INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME) EXEC(@SQL) END TRY BEGIN CATCH PRINT @@ERROR PRINT @SQL END CATCH SELECT @POS = @POS + 1END
SELECT * FROM #TMPREPORT WHERE COUNTER > 0
DROP TABLE #TMPREPORT

Effective Use of Group By

Some times we have to group our data on a specific condition for example in the following table
Create table scenario (studentID smallint, certID smallint, issuedate datetime)GO
populated with data asinsert into scenario --values (studentID, certID, issueDate)select 1,12,'2008-10-13' union all select 1,13,'2009-06-11' union all select 2,16,'2009-08-10' union all select 2,21,'2007-07-23' union all select 3,23,'2009-11-25' GO
I can have a requriement like
"to get records grouped on studentID with last certificate issued according to issue date."
So we can use group by and get required result using the following query
select a.studentID,a.certID,a.issuedatefrom scenario a,(select studentID,max(issuedate)issuedatefrom scenario group by studentID)as bwhere a.studentID = b.studentIDand a.issuedate = b.issuedateorder by a.studentID

Get Table and their total row count

SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME

An Alternate could be


select [Owner] = left(user_name(objectproperty(id, 'OwnerId')), 35)
, [Table] = left(object_name(id), 65)
, [Count] = rows
from dbo.sysindexes
where indid < 2
and objectproperty(id, 'IsUserTable') = 1

Create Data Base Files on Remote Machine

DBCC TRACEON ( 1807 )DBCC TRACEstatus ( 1807 )
USE [master]GO/****** Object: Database [Test4] Script Date: 10/07/2009 18:22:48 ******/CREATE DATABASE [Test4] ON PRIMARY ( NAME = N'Test4', FILENAME = N'\\dnet01\share\Test4.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Test4_log', FILENAME = N'\\dnet01\share\Test4_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_ASGODBCC TRACEOFF (1807)

Enable / Disable All Databbase Triggers

In Sql Server 2005 we can use
sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
To enable all triggers, you can use following statement
sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

Returning Data into a Table Variable

CREATE TABLE dbo.UDFTestTable (Col1 int, Col2 nvarchar(10))GOINSERT dbo.UDFTestTable(Col1,Col2) VALUES (1, N'One')INSERT dbo.UDFTestTable(Col1,Col2) VALUES (2, N'Two')INSERT dbo.UDFTestTable(Col1,Col2) VALUES (3, N'Three')INSERT dbo.UDFTestTable(Col1,Col2) VALUES (4, N'Four')GO
CREATE FUNCTION dbo.UDFReturnsTable (@MinRange int = 1, @MaxRange int = 4)-- Procedure BRETURNS TABLEAS RETURN(SELECT A.Col2 AS N'TextNumber' FROM dbo.UDFTestTable AS A WHERE A.Col1 BETWEEN @MinRange AND @MaxRange)GO
CREATE PROCEDURE dbo.MySP_CallsUDF-- Procedure AAS DECLARE @MyTableVar TABLE (MyTextName NVARCHAR(10)) -- Call the UDF. Note the use of the explicit default value. INSERT @MyTableVar SELECT A.TextNumber FROM dbo.UDFReturnsTable(2,default) AS A SELECT * FROM @MyTableVarGO
-- And see what happens.EXEC dbo.MySP_CallsUDF