Sql Snippets

 

good references:

http://www.1keydata.com/sql/sqlin.html

 

full text indexing:

http://www.kodyaz.com/articles/SQL-Server-Full-Text-Search-Indexing.aspx

 

heaps of awesome scripts, this guy is a genius:

http://vyaskn.tripod.com/code.htm

 

Some complicated selection algorithms, unfortunately written for MySQL, but useful ideas:

http://www.artfulsoftware.com/infotree/queries.php?&bw=1440

 

 

 

---SELECT STATEMENTS-----------

 

SELECT * FROM Product WHERE ProductID=@ProductID 

SELECT * FROM Product WHERE col1 IN ('value1', 'value2') SELECT * FROM Table WHERE PATINDEX('%'+CHAR(13)+CHAR(10)+'%',Column) > 0 -- finds rows where Column contains a line break SELECT TOP 1 column FROM table ORDER BY NEWID() -- gets random row (Windows 2000 or higher) SELECT NEWID() -- gets you a GUID SELECT DATEADD(minute, 3, GETDATE()) AS NewDate -- adds 3 minutes to current time select (varchar,year(activityDate))+'-'+right('0'+convert(varchar,Month(activityDate)),2) from table -- gets you year-date in format YYYY-MM SELECT * FROM Table WHERE column LIKE '%[^a-zA-Z]sell[^a-zA-Z]%' -- finds rows containing "sell" as a whole word (ie not "selling" or "upsell") SELECT * FROM (SELECT TOP(100) FROM Table ORDER BY Date DESC) AS table1 -- useful for UNION below, don't forget the AS alias at the end! SELECT columnA AS column1, columnB AS column2 FROM Table1 UNION SELECT columnC AS column1, columnD AS column2 FROM Table2 SELECT columnA, (SELECT somecolumn FROM sometable WHERE g.id = t.groupid) AS columnB FROM somemaintable t -- using a subquery to fetch some related value from another table SELECT somecolumn + CHAR(10) FROM sometable FOR xml path('')) -- fetch a comma delimited list

---INSERT/UPDATE STATEMENTS----------- 

INSERT INTO Product (Product, Title) VALUES (@Product, @Title); SELECT @NewID = @@Identity

INSERT INTO Store_Information (store_name, Sales, Date) SELECT store_name, Sales, Date FROM Sales_Information WHERE Year(Date) = 1998

UPDATE Product SET Product=@Product, Title=@Title WHERE ProductID=@ProductID

UPDATE TABLE SET FIELD = VALUE FROM TABLE INNER JOIN TABLE1 ON TABLE.ID = TABLE1.ID 

UPDATE Company SET AverageRating = (SELECT AVG(Average) FROM Rating WHERE Rating.CompanyId = Company.CompanyId) (don't use table aliases)

UPDATE Page SET PageNumber = UpdatedPageNumber FROM (select ROW_NUMBER() OVER (ORDER BY page_order) AS UpdatedPageNumber, PageID from Page WHERE SectionID=16) AS Page2 WHERE Page.PageID = Page2.PageID

UPDATE Policy SET

    BasicQuote = newdata.BasicQuote,

    QuoteTotal = newdata.QuoteTotal

FROM ( SELECT * FROM Policy WHERE PolicyID=740 ) newdata WHERE Policy.PolicyID = 726 -- update values in a row from another row 

-- update with exists

UPDATE suppliers  

SET supplier_name = (SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)

WHERE EXISTS (SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id);

-- update from another table

UPDATE table1 SET table1.col = table2.col1 FROM table2 WHERE table2.oldCol = table1.col

 

-- Use OUTPUT clause to get values back on affected records SQL2005 and above only. Can get more than just IDs (better than @@Identity).

INSERT INTO table (col1, col2) OUTPUT INSERTED.tableId, INSERTED.EntryDate VALUES ('val1', 'val2') 

UPDATE Task SET TaskStatus = 'Attempted', AttemptedRunDateTime = GETDATE() OUTPUT INSERTED.TaskID AS TaskID WHERE TaskStatus = 'Pending'

see this for a great explanation: http://www.singingeels.com/Articles/Get_Identity_Field_and_More_From_Inserts.aspx

 

-- make an exact copy of a table into a new table with identies preserved

select * into NewTable from OldTable

-- copy just some columns and rows

select Col1 as NewNameForCol1, Col2 as NewNameForCol2, Col3, Col4 into NewTable from OldTable where Col3='mike'         -- copy just some columns and filter rows

 

-- make an exact copy of a table into a new table with identies preserved (without losing structure, indexes, constraints etc)

truncate table NewTable

set identity insert NewTable on

insert into NewTable select * from OldTable

set identity insert NewTable off

-- copy from a remote database (must be set up with a linked server)

truncate table NewTable

set identity insert NewTable on

insert into NewTable select * from servername.databasename.dbo.OldTable

set identity insert NewTable off

 

 

---DELETE STATEMENTS----------- 

 

DELETE FROM Product WHERE ProductID=@ProductID

DELETE FROM ProductHasExtra FROM ProductHasExtra phe INNER JOIN Extra e ON phe.ExtraID = e.ExtraID WHERE ProductID = 171 AND Category = 'controls' -- note the two FROMs

 

delete t1 from MyTable t1, MyTable t2

where t1.dupField = t2.dupField -- AND t1.dupField2 = t2.dupField2 ...

and t1.uniqueField > t2.uniqueField -- keep one of the records!

 

---ALTER STATEMENTS-----------

ALTER TABLE Product ADD ProductName nvarchar(50)

ALTER TABLE Product DROP COLUMN ProductName

ALTER TABLE Participant ALTER COLUMN PhoneNumber nvarchar(100)

ALTER TABLE Participant ALTER COLUMN PhoneNumber int

ALTER TABLE Participant ALTER COLUMN PhoneNumber bigint -- for when your alter to int overflows

EXEC sp_rename 'TextBlock.PageLinkAvailable', 'IsUrlAvailable', 'COLUMN'; -- rename a column (note: don't think this works on SQL 2000)

EXEC sp_rename 'OldTableName', 'NewTableName' -- rename a table (note: don't think this works on SQL 2000)

DROP TABLE Product

alter table Product add constraint df_ProductIsActive default (0) for IsActive        -- add a default value of 0

 

Rename / resize a column in MS Access the old fashioned way:

ALTER TABLE Ticket ADD COLUMN [tempCol] VARCHAR(150);

update Ticket set [tempCol]=[TicketType];

ALTER TABLE Ticket DROP COLUMN [TicketType];

ALTER TABLE Ticket ADD COLUMN [TicketType] VARCHAR(150);

update Ticket set [TicketType]=[tempCol];

ALTER TABLE Ticket DROP COLUMN [tempCol];

 

..for sql server

ALTER TABLE Newsletter ADD  [tempCol] VARCHAR(255);

update Newsletter set [tempCol]=[BannerURL2];

ALTER TABLE Newsletter DROP COLUMN  [BannerURL2];

ALTER TABLE Newsletter ADD  [BannerURL2] VARCHAR(255);

update Newsletter set [BannerURL2]=[tempCol];

ALTER TABLE Newsletter DROP COLUMN  [tempCol];

 

---CONVERTS-----------

CONVERT(money, AmountField)

CONVERT(float, AmountField)

CONVERT(varchar, AmountField)

convert(nvarchar(4000), somelongtextfield)         -- note it is important to include 4000 which is the max char size, otherwise it will default to some other smaller size

 

--- DATE FORMATTING ----------- 

 

CONVERT(VARCHAR(8), GETDATE(), 3) -- dd/MM/yy

CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy

CONVERT(VARCHAR(9), GETDATE(), 6) -- dd MMM yy

CONVERT(VARCHAR(11), GETDATE(), 106) -- dd MMM yyyy - eg 31 Dec 2009

CONVERT(VARCHAR, GETDATE(), 112) -- yyyyMMdd - good for sorting - eg 20093112

CONVERT(VARCHAR(24), GETDATE(), 113) -- dd MMM yyyy HH:mm:ss:nnn

CONVERT(VARCHAR(12), GETDATE(), 114) -- HH:mm:ss:nnn

format(publishdate,'dd mmm yy')     ' MS Access 

example: select convert(varchar, publishdate, 106) + ' - ' + title from story order by publishdate desc, title

SELECT FLOOR(DATEDIFF(DAY, DateOfBirth, GETDATE()) / 365.25) FROM Rider WHERE RiderID = 315 -- select a date of birth as an age right now

---CASE-----------------

 

CASE WHEN x=1 THEN 'Yes' ELSE 'No' END

 

SELECT FirstName, LastName, Salary, DOB,

  CASE Gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END

FROM Employees

 

SELECT FirstName, LastName, Salary, DOB,

  CASE WHEN Gender='M' THEN 'Male' WHEN Gender='F' THEN 'Female' END

FROM Employees

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

 

 

---ROLLUP & GROUPING()--

 

SELECT

  CASE 

    WHEN GROUPING(Department) = 1 THEN 'Company Average'

    ELSE Department

  END AS Department,

  AVG(Salary) as AvgSalary

FROM Employees

GROUP BY Department

WITH ROLLUP

 

Department       AvgSalary

Sales            78,500.00

Marketing        81,250.00

IT               55,000.50

Executive        91,900.75

Company Average  76,662.81

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

 

 

---INSERT IDENTITY------

-- get rid of offending row if there is one (can't update)

DELETE FROM DealerRegion WHERE DealerRegionID = 13

 

SET IDENTITY_INSERT DealerRegion ON         

INSERT INTO DealerRegion (DealerRegionID, Description, SortPosition) VALUES (11, 'All', 5)

SET IDENTITY_INSERT DealerRegion OFF

 

 

---GENERATE INSERTS FOR DATA------

-- if you want to be able to generate insert statements for your data run this stored proc on the database (it's already on SAM4, no need to re-run)

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

 

then call this to get the scripts: EXEC sp_generate_inserts 'TTEXT_BLOCK'

 

then copy and paste the lines, surround them with IDENTITY_INSERTs

 

 

SET IDENTITY_INSERT [TTEXT_BLOCK] ON  

[paste insert statements here]      

SET IDENTITY_INSERT [TTEXT_BLOCK] OFF

really long lines just get chopped off, so you will need to fix them, but it saves a lot of hassle!

 

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

 

---INSERT MULTIPLE NEW VALUES---

INSERT INTO MyTable  (FirstCol, SecondCol)

SELECT  'First' ,1

   UNION ALL

SELECT  'Second' ,2

   UNION ALL

SELECT  'Third' ,3

   UNION ALL

SELECT  'Fourth' ,4

   UNION ALL

SELECT  'Fifth' ,5

 

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

 

 

---CREATE STORED PROC---

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[MKuspSearch] AS

BEGIN

DECLARE @Param1 int

SELECT @Param1 = 0

END

GO

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

 

 

---CREATE/ALTER FUNCTIONS-----

 

[CREATE|ALTER] FUNCTION ScoreForTrait 

(@TraitId int, @CandidateAssessmentId int)

RETURNS decimal (12,6)

AS

BEGIN

declare @Return decimal (12,6)

set @return = (select SUM(choice_value) from CandidateAssessmentChoice cac 

inner join AssessmentQuestion aq on cac.AssessmentQuestionID = aq.AssessmentQuestionID 

where cac.CandidateAssessmentID = @CandidateAssessmentId and aq.TraitID = @TraitId)

 

return @return

end

 

 

 

---CONFIGURE SERVER-----

sp_configure

 

sp_configure 'show advanced options', 1

 

RECONFIGURE

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

 

 

---CHANGE TABLE OWNER---

sp_changeobjectowner '[dnnadmin].[aspsearch]' , 'dbo' /* will only work if you have sufficient permissions */

 

/* this will change the owner on many tables at once */

DECLARE @old sysname, @new sysname, @sql varchar(1000)

 

SELECT

  @old = 'oldOwner_CHANGE_THIS'

  , @new = 'dbo'

  , @sql = '

  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

  WHERE

      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

      AND TABLE_SCHEMA = ''' + @old + '''

  )

  EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

 

EXECUTE sp_MSforeachtable @sql

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

 

---FIND AND REPLACE-----

UPDATE Page SET bodytexthtml = REPLACE(CAST(bodytexthtml AS varchar(MAX)), 'something', 'somethingelse') WHERE bodytexthtml LIKE '%something%'  

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

 

 

---DELETE LOG FILE------

(right click database - Tasks - Shrink - Files, go from there)

BACKUP LOG mydatabasename WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE(mydatabasename_log,0)

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

 

---WHY ARE LOG FILES SO BIG?------

When backup is run every night, the log is truncated but the log file space is not actually recovered. This is because log files reserve the space they previously had to enable them to easily grow back to that size if needed. In fact this may be much bigger than typically needed, so you can shrink them down if you like and that will recover the lost disk space. Note that log files do have to be a reasonable size or they will not be able to grow fast enough to hold an entire transaction and then you get "log file full" errors.

DBCC SQLPERF (LOGSPACE)            -- tells you how much percent of the log is actually used

 

To shrink all logs run this script, which generates sql statements that you can then run:

 

select 'USE [' + DB_Name(database_id) + '] DBCC SHRINKFILE(''' + name + ''', 0) ' from sys.master_files where type = 1  and database_id > 4 and state_desc='ONLINE'

 

 

 

---DATABASE INFORMATION-

SELECT * FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'Foo'

 

SELECT table_name=sysobjects.name,

         column_name=syscolumns.name,

         datatype=systypes.name,

         length=syscolumns.length

    FROM sysobjects 

    JOIN syscolumns ON sysobjects.id = syscolumns.id

    JOIN systypes ON syscolumns.xtype=systypes.xtype

   WHERE sysobjects.xtype='U'

ORDER BY sysobjects.name,syscolumns.colid

 

SELECT * FROM MSDB.dbo.sysjobhistory -- get reasons of job failure of SQL Agent jobs

 

sp_tables       -- list all tables and views, including system tables

sp_databases     -- list all databases

sp_stored_procedures          -- write out stored procedures

sp_helptext --spnamehere--          -- write out the code for an sp

select * from INFORMATION_SCHEMA.views where table_schema <> 'INFORMATION_SCHEMA'           -- write out views (source code)

select * from sys.database_files       -- if you backup and restore as another database name, these will be out of sync

 

VERSION INFORMATION

select @@version

SELECT SERVERPROPERTY('ProductLevel')          -- this gives values like RTM or SP1

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

 

 

 

To add a linked SQL Server which enabled you to select over multiple servers (eg to copy or compare data):

Sometimes this works better than the GUI for adding logins

 

sp_addlinkedserver 'servername', '', 'SQLNCLI', 'servername', NULL, NULL, NULL  

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'servername',@useself=N'False',@locallogin=N'localdbusername',@rmtuser='remoteDbUsername',@rmtpassword='remoteDbPassword'

 

OR

 

sp_addlinkedserver 'OtherServerFriendlyName', '', 'SQLNCLI', 'OtherServer.com', NULL, NULL, NULL 

sp_addlinkedsrvlogin 'OtherServerFriendlyName', 'false', NULL, 'username', 'password'

 

If it doesn't work, drop and start again:

sp_droplinkedsrvlogin 'OtherServerFriendlyName', 'username'

sp_dropserver 'OtherServerFriendlyName'

 

To select from a linked server use this syntax:

SELECT * FROM servername.databasename.dbo.tablename 

 

To add users:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'servername',@useself=N'False',@locallogin=N'localdbusername',@rmtuser='remoteDbUsername',@rmtpassword='remoteDbPassword'

 

 

----LINKED SERVER PREFIXES-----

 

Sometimes you get the error:

The object name 'servername.databasename.dbo.tablename' contains more than the maximum number of prefixes. The maximum is 2.

 

To solve this, you can try using an alias.

 

 

----PRONTO STUFF---------

SELECT accountcode FROM OPENQUERY([ProntoLive], 'SELECT accountcode FROM deb_master') AS t WHERE t.accountcode = 'XXX'

 

SELECT * FROM sysservers

sp_addlinkedserver [ProntoLive], '', 'MSDASQL', NULL, NULL, 'DSN=XXXXX;Uid=XXXX;Pwd=XXXX;', NULL 

sp_dropserver [ProntoLive]

 

select * from sysoledbusers

sp_addlinkedsrvlogin 'ProntoLive', 'false', NULL, 'XXXX', 'XXXX'

sp_droplinkedsrvlogin 'ProntoLive', NULL

 

date format to use in WHERE clauses:

SELECT * FROM OPENQUERY([ProntoLive], 'SELECT * FROM table WHERE data

 

dates: {d 'yyyy-mm-dd'}

timestamps: {ts 'yyyy-mm-dd hh:mm:ss'}

times: {t 'hh:mm:ss'}

 

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

 

 

-- RANDOM NUMBER PER ROW -----

 

CREATE VIEW vRandNumber AS SELECT RAND() as RandNumber

 

The view is necessary because normally in a UDF we cannot use the rand() function, because that would make the function non-determistic.  We can trick the UDF to accepting a random number by using a View.

 

Once that is set up, we can then create our function:

 

CREATE FUNCTION RandNumber(@Min int, @Max int)

RETURNS float

AS

 BEGIN

 RETURN @Min + (select RandNumber from vRandNumber) * (@Max-@Min)

 END

 

Finally, you can use this function in any SELECT to now return a random number between 0 and 1 per row:

 

SELECT dbo.RandNumber(0,1)

 

~~OR~~

if you want a random whole number:

 

CREATE FUNCTION RandWholeNumber(@Min int, @Max int)

RETURNS int

AS

 BEGIN

 RETURN FLOOR((select RandNumber from vRandNumber) * (@Max - @Min + 1) + @Min)

 END

 

use like this: 

SELECT dbo.RandWholeNumber(1,453)

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

 

Find duplicates

 

SELECT *

FROM dbo_CompetitionEntry

WHERE (((dbo_CompetitionEntry.[EmailAddress]) In (SELECT [EmailAddress] FROM [dbo_CompetitionEntry] As Tmp GROUP BY [EmailAddress],[FirstName],[LastName] HAVING Count(*)=1  And [FirstName] = [dbo_CompetitionEntry].[FirstName] And [LastName] = [dbo_CompetitionEntry].[LastName])))

ORDER BY dbo_CompetitionEntry.[EmailAddress], dbo_CompetitionEntry.[FirstName], dbo_CompetitionEntry.[LastName];

 

 

-- RANKING -----

-- get a rank column in your results

select rank() OVER (ORDER BY VoteWin) as rank, RecipeID from Recipe r WHERE IsFinalist = 1

 

-- find the rank of RecipeID 17066

select rank FROM (

select rank() OVER (ORDER BY VoteWin) as rank, RecipeID from Recipe r WHERE IsFinalist = 1

) as s WHERE RecipeID = 17066 -- this will return "6"

 

-- if there are a number of records with the same value in the ORDER BY column, ranking will be the same for each i.e.

RankingCol, OrderByCol

1, 10

1, 10

1, 10

4, 20

5, 30

 

-- if you want an individual number per row, you can use ROW_NUMBER() like this:

select ROW_NUMBER() OVER (ORDER BY VoteWin) as RowNumberCol, VoteWin from Recipe r WHERE IsFinalist = 1

 

-- to give this:

RowNumberCol, OrderByCol

1, 10

2, 10

3, 10

4, 20

5, 30

 

 

-- PERFORMANCE AND SQL SESSIONS -----

/*Read more: DMV Query to identify all active SQL Server Sessions http://www.johnsansom.com/dmv-query-to-identify-all-active-sql-server-sessions/#ixzz1XIrCeYdG 

Copyright John Sansom 2011. All rights reserved */ 

 

--sql to check all running queries is (and possibly kill them)

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

--While running above query if you find any query which is running for long time it can be killed using following command.
--KILL [session_id]

--Reference : Pinal Dave
--http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

-- memory taken by databases

SELECT

(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',

(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',

COUNT (*) AS 'Page Count'

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id], [is_modified]

ORDER BY [database_id], [is_modified];

GO

 

-- top 20 sql statements by cpu time

SELECT TOP 20

    qs.sql_handle,

    qs.execution_count,

    qs.total_worker_time AS Total_CPU,

    total_CPU_inSeconds = --Converted from microseconds

    qs.total_worker_time/1000000,

    average_CPU_inSeconds = --Converted from microseconds

    (qs.total_worker_time/1000000) / qs.execution_count,

    qs.total_elapsed_time,

    total_elapsed_time_inSeconds = --Converted from microseconds

    qs.total_elapsed_time/1000000,

    st.text,

    qp.query_plan

FROM

    sys.dm_exec_query_stats AS qs

        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

        CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC

 

-- current sessions on server

SELECT B.login_name, A.client_net_address, NoOfConnections = COUNT(*) FROM sys.dm_exec_connections A INNER JOIN sys.dm_exec_sessions B ON A.session_id = B.session_id GROUP BY login_name, client_net_address 

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

-- INDEXES -----

CREATE INDEX index_name ON table_name (column_name, column_name2)

DROP INDEX index_name  ON table_name

-- REINDEX all tables in current database

EXEC sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'

 

-- REINDEX all tables in all databases

DECLARE @SQL  NVarchar(4000)

SET @SQL = ''

 

SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC

DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)

FROM MASTER..Sysdatabases

 

PRINT @SQL

EXEC (@SQL)

 

---RECOMMENDED MISSING INDEXES IN CURRENT DATABASE---

 

This is awesome. It prints out a list of all recommended indexes, that you can easily add to your database to improve performance.

**Note** you will need to delete indexes before you can delete columns, so only do this once database is pretty much finalised.

 

To get your database id use this: select db_id('yourdbname')

However the following script just uses the current database you are in, so you don't actually need to specify an ID.

 

SELECT TOP 250

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +

CASE

WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

 

(Note: to get missing indexes for all databases, just remove the Where clause - in italics) 

 

---UNUSED INDEXES---

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME], type_desc,
coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) as LastUsed,
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES ,
last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup,
'drop index ['+I.[NAME]+'] on ['+OBJECT_NAME(S.[OBJECT_ID])+'];' as DropStatement
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
order by type_desc,coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) desc

---DELETE ALL CONSTRAINTS IN A DATABASE---

/* from http://social.msdn.microsoft.com/Forums/en/transactsql/thread/219f8a19-0026-49a1-a086-11c5d57d9c97 */

 

declare @str varchar(max)

declare cur cursor for

 

SELECT 'ALTER TABLE ' + '[' + s.[NAME] + '].[' + t.name + '] DROP CONSTRAINT ['+ c.name + ']'

FROM sys.objects c, sys.objects t, sys.schemas s

WHERE c.type IN ('C', 'F', 'PK', 'UQ', 'D')

 AND c.parent_object_id=t.object_id and t.type='U' AND t.SCHEMA_ID = s.schema_id

ORDER BY c.type

 

open cur

FETCH NEXT FROM cur INTO @str

WHILE (@@fetch_status = 0) BEGIN

 PRINT @str

 EXEC (@str)

 FETCH NEXT FROM cur INTO @str

END

 

close cur

deallocate cur

 

 

---COLLATION ERROR---

Error is something like: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

 

Probably you have a join between two databases, something like this:

LEFT OUTER JOIN Tracking t ON AnotherDatabase.dbo.Table.AccountNumber = t.AccountNumber

 

add in COLLATE DATABASE_DEFAULT (probably will only be needed after one of the fields - the one from the other database, but you can do on both)

LEFT OUTER JOIN Tracking t ON AnotherDatabase.dbo.Table.AccountNumber COLLATE DATABASE_DEFAULT = t.AccountNumber

 

from: http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

---DELETE/DROP USER ERROR---

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

 

-- find out what schemas the user owns:
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('callum');
-- change the owner to dbo:
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo;
-- now you can drop the user:
drop user callum

 

 

---alter database failed because lock could not be placed---

Find out the SPID of the process using your database:

EXEC sp_who2

then kill it:

KILL 87

 

---RECREATE USERS AFTER RESTORING A BACKUP---

When you restore a backup from a different machine, the Database User may be created but not connect properly with its associated Server Login. This is because sql server uses its internal ID and not the actual name.

If you use the tools to add the User Mapping back, it says "user already exists with this name" but it won't connect it. You can either delete the user and then add the mapping again, or run the following sql:

sp_change_users_login 'report'                                         -- will tell you if there are any orphaned users

sp_change_users_login 'update_one', 'myloginname', 'myusername'         -- login and user are often the same name

 

---FIX A SQL INJECTION ATTACK---

General procedure:

1. Get IIS log files

2. Search all available log files for likely SQL keywords - most likely EXEC, DECLARE, CAST

3. Copy the SQL text into an URL decoder (find one on google) 

4. Change any EXEC statements to PRINT and check the SQL code to ensure it is now harmless

5. Run against a local copy of the database and see what it does

6. Then you will be able to check the fields it is updating for damage

7. Patch up the holes (on any URLs found in log files)

8. Check IIS Request Filtering is installed (under Role options) and enabled (need to install IIS Admin Pack) and has appropriate rules set up

    Suggested patterns to block are: "EXEC(@", "DECLARE @"

9. If it is a tagging attack, you should be able to fix the data without having to restore from backup.

To fix the data, you can use a similar approach to the attacker, scan all the tables that they scanned looking for what they injected and remove it.

 

For example this will print out a sql statement to search each column for damage:

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('nvarchar','varchar','ntext','text') and c.CHARACTER_MAXIMUM_LENGTH>80 and t.table_name=c.table_name and t.table_type='BASE TABLE' OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN print('select '''+@T+''','''+@C+''', count(*) from ['+@T+'] where ['+@C+'] like ''%some crap injected by a hacker%'' ') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

 

And this will print out a sql statement to fix each column:

DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('nvarchar','varchar','ntext','text') and c.CHARACTER_MAXIMUM_LENGTH>20 and t.table_name=c.table_name and t.table_type='BASE TABLE' OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN print('update ['+@T+'] set ['+@C+']=replace(convert(nvarchar(4000),['+@C+']), ''some crap injected by a hacker'', '''') where ['+@C+'] is not null and ['+@C+'] like ''%some crap injected by a hacker%'' ') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

 

 

---ADD AN INDEX WITH NO DOWNTIME---

General procedure:

- copy data into temp table

- trunc table

- add index

- copy data back from temp table, preserving identities