Articles in categories

Now on Amazon

Quickly list all foreign key dependencies for a table

To get a quick list of all foreign key dependencies for a specific table in SQL Server use:

EXEC sp_fkeys 'tableName'

 It will give you a list with all FK dependencies for that table.

 

Search recently created or modified Stored Procedures

Search for recently modified Stored Procedures

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 5 -- modified last 5 days

 

Search for recently created Stored Procedures

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 5 -- created in the last 5 days

 

Copy Stored Procedures between databases

Ever need to copy Stored Procedures, Views or other objects from one database to another?
Here's how to do it.

  1. Rightclick the database and select Tasks --> Generate scripts
    db_copy_sp_1.jpg
  2. A wizard will open.
    db_copy_sp_2.jpg
  3. Select the objects, in my case Stored Procedures, you want to copy.
    db_copy_sp_3.jpg
  4. Specify how scripts should be saved or published.
    db_copy_sp_4.jpg
  5. Run the script.
    db_copy_sp_5.jpg

Keep in mind that when the copy script is created a USE keyword is used. You need to remove it or change it to the correct database.

 

Data from sql table to xml

I needed to retrieve some data from an sql server table and put it in an xml file. That data I wanted to use in my application, but because I wasn't allowed to connect with the database directly I needed a way to create an xml file.

This is how I need the xml file to look like (dummy data):

Sql_to_xml

And this is the sql query that will create it:

SELECT * 
FROM Person
FOR XML AUTO, ROOT ('Persons'), ELEMENTS

You can do a lot more with the FOR XML statement. More info can be found here: Basic Syntax of the FOR XML Clause.

Maybe I'll add some more examples later.

 

SQL Server Date Formats

Whenever I want to format the Sql Server date field to a specific format I had to search the web how to do it.
Now I've made myself a list with the most frequent date formats and their sql syntax. It includes an example of what the output will be.

The Transact-SQL (T-SQL) CONVERT() function is used to convert data between different types. When converting a DATETIME value to a VarChar string value a style code may be applied. See the list below for the code to use and the output format.
MSDN: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Sql date time functions: http://msdn.microsoft.com/en-us/library/ms186724.aspx

-- DateTime formatting in SQL Server

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
-- Jul 30 2012  7:59PM

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
-- 07/30/12

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
-- 07/30/2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
-- 12.07.30

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
-- 2012.07.30

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
-- 30/07/12

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
-- 30/07/2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
-- 30.07.12

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
-- 30.07.2012

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
-- 30 Jul 12

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
-- 30 Jul 2012

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
-- Jul 30, 12

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
-- Jul 30, 2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
-- 20:06:13

SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
-- Jul 30 2012  8:06:13:077PM

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
-- 07-30-12

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
-- 07-30-2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
-- 12/07/30

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
-- 2012/07/30

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
-- 120730

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
-- 20120730

SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
-- 30 Jul 2012 20:07:36:890

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
-- 20:07:36:890

SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
-- 2012-07-30 20:08:59

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
-- 2012-07-30 20:08:59.333

SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
-- 2012-07-30T20:08:59.333

SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
-- 12/09/1433  8:08:59:333PM