-- query to find all foreign keys and referenced tables/columns

SELECT obj.name AS FK_NAME,
schem.name AS [schema_name],
Table1.name AS [table],
Columns1.name AS [column],
table2.name AS [referenced_table],
Columns2.name AS [referenced_column]
FROM sys.foreign_key_columns fkCol
INNER JOIN sys.objects obj
ON obj.object_id = fkCol.constraint_object_id
INNER JOIN sys.tables Table1
ON Table1.object_id = fkCol.parent_object_id
INNER JOIN sys.schemas schem
ON Table1.schema_id = schem.schema_id
INNER JOIN sys.columns Columns1
ON Columns1.column_id = parent_column_id AND Columns1.object_id = Table1.object_id
INNER JOIN sys.tables table2
ON table2.object_id = fkCol.referenced_object_id
INNER JOIN sys.columns Columns2
ON Columns2.column_id = referenced_column_id
AND Columns2.object_id = table2.object_id

@Rank vs @Row_Number

So recently I was asked the difference between the two and my brain froze.  There are other phrases that come to mind, but the condition of frozen brain is more PC and I'm sticking to it.

Back on topic; SQL provides a few "Ranking Functions" that can be most helpful.  There is a subtle difference between @Rank and @Row_Number I will try to demonstrate here, starting with a table called "Inventory".  For purposes of demonstration, we will assume that we can have multiple records of the same inventory item at the same location with the same quantity.  Here is the Inventory table:

The following SQL exposes the difference in the two functions:

select recordid, productid, description, location, quantity
,rank() over (partition by produtctid order by quantity desc) as rank
,row_number() over (partition by productid order by quantity desc) as row_number
from inventory
Results:

Highlighted above are the @Rank and @Row_Number for ProductID 101 at Location 1. 
Notice, that for every Product 101 at Location 1 that @Row_Number increments by 1.
@Rank, in comparison, does not. Duplicates do not cause @Rank to increment.  On rows 1 and 2, the value of @Rank is 1 - these are the number 1 values.  ODDLY though, if we look on rows 3 and 4 (also duplicate records) we see that @Rank continues with a value of 3.  This is because there are 2 records in ahead of it.

Conclusion:  @Rank and @Row_Number have different outcomes when duplicate values are present for the Partition By and Order By clauses.  

All About SQL Joins

For purposes of demonstration, lets imagine we have a zoo.
The zoo database has a table of animals it keeps named Animals.
It also has a table of the soft stuffed animals it sells in the Products table.

You have been asked to provide the following:

Find each Animal where there is not a corresponding Product:

Select A.*, P.*
from Animal A
left join Product P
on A.Animal = P.Product
where P.Product is null


Find all the Animals where there is not a corresponding Product
AND all the Products where there is not a corresponding Animal

Select A.*, P.*
from Product P
full join Animal A
on P.Product = A.Animal
where A.Animal is null or P.Product is null


Find each Animal at the zoo where there is also a corresponding Product:


select A.*, P.*
from Animal A
join Product P
on A.Animal = P.Product




Find each Animal at the zoo and also each Product whether there is a match or not:

Select A.*, P.*
from Animal A
Full join Product P
on A.Animal = P.Product

Remove Spaces and Non Alpahnumeric Characters

SELECTRTRIM(CUSTNMBR)AS CUSTNMBR
,REPLACE(REPLACE(CUSTNMBR ,SUBSTRING(CUSTNMBR ,PATINDEX('%[^a-zA-Z0-9 ]%' ,CUSTNMBR) ,1) ,'') ,CHAR(32) ,'')AS NU_CUSTNMBR

,CUSTNAME


FROM RECORDSTABLE


WHERECUSTNMBR LIKE '%[^a-zA-Z0-9 ]%'

OR CHARINDEX(CHAR(32) ,RTRIM(CUSTNMBR)) > 0;

Find instances of a string in all your stored procedures:

This is a great little trick I came across while we were moving from one server to another and had to change the prefix on all references to that SQL machine - but it work in any instance where you need to search through all your stored code:

USE <databasename>; go   SELECT routine_name               ,routine_definition FROM information_schema.routines WHERE routine_definition LIKE '%<my search string>%'   AND routine_type = 'PROCEDURE' ORDER BY routine_name;

But it worked yesterday! 

Your SQL window just returned the following error message:

OLE DB provider 'SQLNCLI10' for linked server 'XX01' returned data that does not match expected data length for column '[xx01].[dbname].[DBO].[tablename].fieldname'. The (maximum) expected data length is 30, while the returned data length is 35.

Funny thing is, when you run it outside of a stored procedure it works fine. 
What's up with that?


What you're probably looking at is compiled code you've written (stored procedure, function, etc) which accesses a view that has been recently altered.  When a non-schema bound view is created, the meta-data from what it returns is stored on any linked servers.  Sounds messy? It is, and also a fair argument against non-schema bound views, but just the same, we need to know how to deal with them.

As you will find in http://msdn.microsoft.com/en-us/library/ms187821.aspx, the answer is to refresh the view with the following syntax:
EXECUTE sp_refreshview 'viewname'

Now here's the really odd part...... you execute the refresh from the server where the view exists, not the server linked to it.  Go figure.  If anyone has a logical answer please fill us all in!

Cheers

Script to retrieve temp table definition

So you created a query using a select into to create a temp table.....

And now you you need to productionalize it.  First order; get rid of the select into that is tying up your TempDB.

But how to go back and find out the size and datatype of all those columns you just stuffed into the temp table.  A select into requires none of that, right?  Going to each of the individual tables and getting the definitions can be time consuming.  Except....


USE TEMPDB;

SELECT

    c.COLUMN_NAME

    ,c.DATA_TYPE

    ,c.CHARACTER_MAXIMUM_LENGTH

    ,c.NUMERIC_PRECISION

    ,c.NUMERIC_SCALE

FROM INFORMATION_SCHEMA.COLUMNS c

join INFORMATION_SCHEMA.TABLES t

    on c.TABLE_NAME = t.TABLE_NAME

where

    c.TABLE_NAME like '#your_table_name%'

ORDER BY

    c.TABLE_NAME

    ,c.ORDINAL_POSITION

 

 

Strip unwanted characters out of sql string or variant data

-- =============================================

-- Author:           blowers

-- Create date: 2012-05-11

-- Description:      function to strip out special (escape and other) characters from an input string

-- MODIFY @KEEP TO INCLUDE THE CHARACTERS YOU WISH TO KEEP

--     - in the example a-z, 1-9 and a period will be retained and all others removed

--     - go be the uber-dba and create a list of formats you might want to use (numeric only, alpha only, money formatting only, etc)

-- =============================================

CREATE Function [dbo].[strip_special](@Temp VarChar(1000))

Returns VarChar(1000)

AS

       Begin

              DECLARE @KEEP VARCHAR(50)

              SET @KEEP = '%[^a-z0-9.]%'

              While PatIndex(@KEEP, @Temp) > 0

                     Set @Temp = Stuff(@Temp, PatIndex(@KEEP, @Temp), 1, '')

              Return @TEmp

       End

GO

QUOTENAME : A little known util function for "bracketing" a value

DECLARE @TEXT VARCHAR(50)
DECLARE @NUMBER INT
DECLARE @QUOTE CHAR(1)

SET @QUOTE = '"'

SET @TEXT = 'hello'

SET @NUMBER = 13

--bracketed with default brackets

SELECT  QUOTENAME(@TEXT), len(@TEXT), len(quotename(@TEXT))

--bracketed with default brackets

--note that an implicit conversion occurs)

SELECT  QUOTENAME(@NUMBER)

--bracketed with a double quote

SELECT  QUOTENAME(@TEXT, @QUOTE)

Great SQL Brain Teaser.... how many records are in the table?

Create Table TBL1 (col1 int, col2 int)

Create Table TBL2 (col1 int, col2 int)

 

--query1

select count(col1) from TBL1 where col2 >= 5

--results = 3

 

--query2

select count(col1) from TBL1 where col2 < 5

--results = 2

 

-- can you tell how many records are in the table?

-- how/why?

Restarting SSIS Packages without processing ALL the records over again

Here is a link to a little-known solution to a common problem.  Every time I have restarted a SQL SSIS package in the past that performs transformations on records, all the records get processed over again. 

This is a problem in a couple respects:

• Processing time wasted
• Code must check for records already processed
• Additional time is spent maintaining that code and procesing

Here is the link:
Restarting SSIS Packages with Checkpoints

While this article uses SQL 2008 as an example, SQL 2005 also employes this same feature.

Dynamic SQL Passthrough Queries with Parameters

If you need to run a passthrough query against another DBMS and need to create that SQL on the fly because of a parameter, then you may be out of luck. At least, according to MSDN:
http://msdn.microsoft.com/en-us/library/ms188427.aspx

However, there is a nice workaround you can employ that will do the job. Here is an example:

-- variable to contain the passthrough sql statement
DECLARE @SQL VARCHAR(300)
-- variable to contain the dynamic lookup value into the query
DECLARE @KeyLookup INTEGER
-- varliable to contain the passthrough query
DECLARE @query VARCHAR(400)
-- populate the keylookup value
SET @KeyLookup = 102
-- prepare the sql statment for the passthrough query
SET @query = 'select * from some_table where key_value = ' + CONVERT(VARCHAR(10) , @KeyLookup)
-- prepare the passthrough execution query
SET @sql = 'select * from openquery(linkedservername, ''' + @query + ''')'
-- option to print the passthrough execution query in full for debug purposes (this is nice because you can paste it into a sql
-- editor and run it to see what is wrong)
PRINT @SQL
-- execute the passthrough
EXEC (@SQL)

Creating System Stored Procedures

So what are the steps to creating a systemwide stored procedure in SQL Server?

• Create them in the Master Database
• Name must start with "sp_"
• Mark them as System Objects using
• for sql 2000 - master.dbo.sp_MS_upd_sysobj_category
• For 2005 and later - sys.sp_MS_marksystemobject

For a test in 2008:

1. Create a stored procedure in the system databaseone called sp_test
2. Register them as system stored procs using sp_MS_marksystemobject.
3. Then try to use them each from another database without prefixing them with master.dbo.

How to send IM text when a SQL job fails

/* this is very useful for critical production jobs that may fail at times when you do not have access to your email, but can be contacted via SMS Text Messaging on your CellPhone*/

set nocount on
declare @message varchar(555)
set @message = 'Process failed in job xyz'

-- different providers have different addresses to send SMS to cell phone --
/* T-Mobile: phonenumber@tmomail.net
Virgin Mobile: phonenumber@vmobl.com
Cingular: phonenumber@cingularme.com
Sprint: phonenumber@messaging.sprintpcs.com
Verizon: phonenumber@vtext.com
Nextel: phonenumber@messaging.nextel.com
where phonenumber = your 10 digit phone number */

exec master.dbo.xp_sendmail @recipients = '5555551212@messaging.sprintpcs.com' , @message = @message , @subject = 'Svr263 Job Failure'