Comma delimited string to table

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

DECLARE @String VARCHAR(10)

WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))

INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END

RETURN
END
GO

SQL Server performance counters are missing in Windows 64bit

When you run the PERFMON on a 64 bit windows server you are actually running the 64bit version of the application which can only load 64bit counters, but not counters for a local 32-bit applications. To workaround the problem, launch the application using /32 switch, like this mmc /32 perfmon.msc

Which action fired the trigger?

Using the following logic you can identify which action cased the trigger to fire

if exists (select 1 from deleted) and exists (select 1 from inserted)
print ‘Update’

if exists (select 1 from inserted) and not exists (select 1 from deleted)
print ‘Insert’

if exists (select 1 from deleted) and not exists (select 1 from inserted)
print ‘Delete’

Display trigger status

--version sql server 2000

SELECT
name,
status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
THEN 'Enabled' ELSE 'Disabled' END,
owner = OBJECT_NAME (parent_obj)
FROM
sysobjects
WHERE
type = 'TR'

Rotate a table in SQL Server

Rows to Column

Source Table

Year|Quarter|Amount
========================
1995|1 |125,000.90
1995|2 |136,000.75
1995|3 |212,000.34
1995|4 |328,000.82
1996|3 |728,000.35
1996|2 |422,000.13
1996|1 |328,000.82

Result Table

YEAR | Q1 | Q2 | Q3 | Q4
-------------------------------------------------------------------
1995 | 125,000.90 | 136,000.75 | 212,000.34| 328,000.82
1996 | 328,000.82 | 422,000.13 | 728,000.35| 0.00


SQL

SELECT year=q.year,
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
FROM qtrsales q
GROUP BY year

Columns to Row

Source Table

ID | RefDate |Rcvd Date
----------------------------
1 | 01/01/04| 02/01/04
2 | 05/01/04| 07/01/04
3 | 07/01/04| 12/01/04

Result Table

ID | Date | Type
----------------------------
1 | 01/01/04| RefDate
1 | 02/01/04| RcvdDate
2 | 05/01/04| RefDate
2 | 07/01/04| RcvdDate
3 | 07/01/04| RefDate
3 | 12/01/04| RcvdDate

SQL

SELECT ID, RefDate, 'RefDate' AS Type FROM myTable
UNION
SELECT ID, RcvdDate, 'RcvdDate' AS Type FROM myTable

How to transfer database diagrams to a different database

Database diagrams are stored in the 'dtproperties' table within the database. So, database diagrams can be transferred to a different database, by transferring the contents of this table.

For example , run the following query to transfer the diagram named 'MyTableDesign' from 'pubs' database to 'northwind':

INSERT northwind..dtproperties
SELECT objectid, property, value, lvalue, version
FROM pubs..dtproperties
WHERE objectid =
(
SELECT objectid
FROM pubs..dtproperties
WHERE value='MyTableDesign'
)

Make sure, the tables referenced by these diagrams already exist in the target database, or else these diagrams won't show up in the target database.

Moving SQL Server 2000 databases to another machine

1.Detach all user databasese from source server
2.Stop SQL Server service on the source server
3.Copy the master, msdb and model database to the destination server

Attaching MSDB database on to destination server

To attach the MSDB database, you must startt SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the "Moving the model database" section. After you add the -c option, the -m option and trace flag 3608, follow these steps:
1. Stop, and then restart SQL Server.
2. Make sure that the SQL Server Agent service is not currently running.

3. Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
5. Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.
6. Stop and then restart SQL Server.
7. Reattach the msdb database as follows:
use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'
go

attaching the master database on to destination server

1. Change the path for the master data files and the master log files in SQL Server Enterprise Manager.

Note You may also change the location of the error log here.
2. Right-click the SQL Server in Enterprise Manager and then click Properties.
3. Click Startup Parameters to see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.
4. Change these values as follows:
a. Remove the current entries for the Master.mdf and Mastlog.ldf files.
b. Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf


5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server

Moving Temp database
You can move tempdb files by using the ALTER DATABASE statement.

1. Use the ALTER DATABASE statement, specifying the logical file name as follows:use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
2. Stop and then restart SQL Server.

Attaching User database
1. use exec sp_attach_db
use master
go
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
go

To enable SQL Authentication for the default instance using T-SQL

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft
\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

or for a named instance:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft
\Microsoft SQL Server\\MSSQLServer\', N'LoginMode',
REG_DWORD, 2

Selecting Nth row from SQL Server 2000 table

Scritpt bellow explain how to get Nth row from SQL Server 2000 table


SELECT * FROM
(
SELECT *, (SELECT COUNT(*) FROM MyTable t2 WHERE t2.ID<=t1.ID) rn FROM MyTable t1
) x WHERE rn%5=1



where 5 is the column number

Top 20 large tables

--sql 2000

declare @topcount int
set @topcount = 20

declare @pagesize bigint
declare @dbid int


select @pagesize = low
from master.dbo.spt_values
where number = 1
and type = 'E'

declare @spt_space table
(
objid int null,
rows int null,
reserved int null,
data int null,
indexp int null,
unused int null
)

insert into @spt_space
select objid = id,
rows = sum(case when indid in (0, 1) then rowcnt else 0 end),
reserved = sum(case when indid in (0, 1, 255)
then reserved
else 0
end) * @pagesize / 1024 /1024,
data = sum(case when indid in (0, 1) then dpages
when indid = 255 then used
else 0
end) * @pagesize / 1024/1024,
indexp = sum(case when indid in (0, 1, 255)
then used
else 0
end) * @pagesize / 1024/1024,
unused = sum(case when indid in (0, 1, 255)
then used
else 0
end) * @pagesize / 1024/1024
from sysindexes
where rowcnt > 0
group
by id, indid
order
by reserved desc

set rowcount @topcount
select Table_Name = name,
rows,
reserved_MB = ltrim(str(reserved,15,0) + ' ' + 'MB'),
data_MB = ltrim(str(data,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp - data,15,0) + ' ' + 'MB'),
unused_MB = ltrim(str(reserved - unused,15,0) + ' ' + 'MB'),
idx_data_ratio = ltrim(str((indexp - data)*100 /data) + '%'),
unused_pct = ltrim(str((reserved - unused) * 100 /reserved) + '%')
from @spt_space s join sysobjects o on o.id = s.objid and xtype = 'U'
where data > 0
order
by reserved desc
set rowcount 0

Blocking information

SELECT datediff(mi,last_batch,getdate())LockTime,P.SPID, P.BLOCKED BLOCKEDBY, RTRIM(P.HOSTNAME) HOSTNAME,sql_handle
FROM MASTER..SYSPROCESSES P
WHERE P.BLOCKED <> 0
AND SPID<>BLOCKED
OR SPID IN (SELECT BLOCKED FROM MASTER..SYSPROCESSES)
AND BLOCKED IN (SELECT SPID FROM MASTER..SYSPROCESSES)
AND BLOCKED=0
ORDER BY P.BLOCKED, P.SPID

Identifying log running/cpu intensive SQL query using Performance Monitor

Identifying log running/cpu intensive SQL query using Performance Monitor

This article describe how to identify a log running sql script using Performance Monitor tool
1. Launch the System Performance tool from, Administrative tools-> Performance
2. Select Thread from Performance Object drop down box
3. Select % Processor time and ID Thread from counter list box
4. Select all sqlsrv instance from instance list and click add button
5. Change the view style to report form

You can identify the long running/cpu intensive SQL Server thread by watching the % process time column.
To get the SQL statement that it currently executing use

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE kpid = 1000--(replace it with the ID Thread value)
SELECT * FROM ::fn_get_sql(@Handle)

Starting/Stoping SQL Server service on remote machine

sc [ServerName] Command ServiceName [ServiceArguments]

ServerName
Specifies the name of the server where the service will be started or stopped. Enter the ServerName in UNC format .
ServerName is not needed if you are stopping/starting a local service.

Command
The command is either “start” or “stop”.

ServiceName
The service name to be started or stopped.

ServiceArguments
Specifies service arguments to pass to the service being started.

Note: this option is not used when the command “stop” is issued.
For more information see http://www.databasejournal.com/features/mssql/article.php/3644906

Script to find out specific word in database

DECLARE @SSQL VARCHAR(200)
SELECT @COLUMN = '%' + @COLUMN + '%'



SET NOCOUNT ON

SELECT @SSQL = 'TABLES THAT CONTAIN COLUMNS THAT BEGIN WITH ' + @COLUMN
SELECT @SSQL
PRINT '----------------------------------------------------------'
SELECT CONVERT(VARCHAR(60), O.NAME) "TABLE NAME"
FROM SYSCOLUMNS C, SYSOBJECTS O, MASTER.DBO.SYSTYPES T
WHERE C.ID = O.ID AND C.XTYPE = T.XTYPE
AND C.NAME LIKE @COLUMN
AND O.TYPE = 'U'
GROUP BY O.NAME

/*FOR STORED PROCEDURES, ADD WILDCARD TO BEGINNING BECAUSE COLUMN WILL BE CONTAINED WITHIN TEXT*/
SELECT @COLUMN = '%' + @COLUMN

SELECT @SSQL = 'PROCS THAT CONTAIN THE WORD ' + REPLACE(@COLUMN, '%', '')
SELECT @SSQL
PRINT '----------------------------------------------------------'
SELECT CONVERT(VARCHAR(60), O.NAME) "NAME",
CASE WHEN O.XTYPE='P' THEN 'STORED PROCEDURE'
WHEN O.XTYPE='TR' THEN 'TRIGGER'
WHEN O.XTYPE='V' THEN 'VIEW'
WHEN O.XTYPE='FN' THEN 'FUNCTION'
END [OBJECT TYPE]
FROM SYSOBJECTS O, SYSCOMMENTS C
WHERE O.ID = C.ID
AND C.TEXT LIKE @COLUMN

SET NOCOUNT OFF