Two handy queries which can help you find things in your database


--Search for a specific string in object-definitions:
SELECT DISTINCT
o.name AS Object_Name
,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%SELECT%'

--Find a column within the database:
SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Customer%'
ORDER BY SchemaName, ColumnName









Advertisements

T-SQL Metadata Queries

Stumbled upon this great post by Marco Schreuder about how to collect source system metadata with T-SQL queries:
The blog post is a few years old, but still the queries might come in handy.

TSQL Metadata Queries

The queries:

1. Get column information from all tables
2. Get column information from all views
3. Get details of foreign key constraints
4. Get details of the indices created on user tables








How to calculate the number of working minutes…FAST :)

This is always a performance thing, ETL-wise, but also in stored procedures.
I’ve published an example on how to calculate the number of working days (rounded) via a scalar-valued function:
Function: Calculate working days

This example however calculates the working minutes between dates, while taking into account the company and/or national holidays and weekends.
You could extend the functionality to also include local office hours.

Three examples:
1) The number of working minutes between a given date/time-value and the present (NOW)
2) The number of working minutes between two given date/time-values
3) The number of working minutes between two given date/time-values from a large dataset

Number-of-working-minutes-between-dates-sql-server-set-based

Testing results:
I ran a test on a large dataset which included actual order dates and after the so called ‘WorkingMinutes’-table was made in 4~5 seconds, it took only 3 seconds to process more than 1 million records!

Download the SQL script here:
WorkingMinutes-SQL-Examples








SET DATEFORMAT, a thing to remember.

Today I faced with an issue that an older SQL 2000 server (source db) had different formatting settings (my guess is the OS’s regional settings) than my DWH server (target db) or my local machine. Googled a bit and found the ‘SET DATEFORMAT‘-statement:
SET DATEFORMAT { dym | dmy | mdy | myd | ydm | ymd }

An easy method to check which format setting the server is applying/using, is to execute this SQL query:
SELECT DatePart(Month, '11-7-2014') AS CurrentMonth


How it works

SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '07/11/14'
SELECT @datevar AS CurrentDate
GO

SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '14/11/07'
SELECT @datevar AS CurrentDate
GO

SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '14/07/11'
SELECT @datevar AS CurrentDate
GO

The results:

sql-set-dateformat-results








SQL: A neat trick to create a SELECT-query which will include all the column names

For this one you’ll need to thank SQL Chick (Melissa Coates)
Based on here blog post:
http://www.sqlchick.com/entries/2014/2/1/shortcut-to-script-individual-columns-from-a-table-valued-function-in-ssms

Basically start building a basic SELECT-query with ‘Select *‘, then select it all (CTRL+A) and hit CTRL+SHIFT+Q to open up the Query Designer. Now you can easily copy/paste the query which it has generated for you and this one does include all the column names! 😉

Neat-trick-to-create-a-select-statement-which-includes-all-columns-handy








Table Valued Constructors: insert multiple rows of hard-coded values into a table

This one I borrowed from Plamen Ratchev:
http://pratchev.blogspot.nl/2008/01/table-value-constructors-in-sql-server.html

One of the new features of SQL Server 2008 is the support for table value constructors (part of ANSI SQL). Here are a couple quick examples of using them.
-- Populate sample table

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol VARCHAR(30));

In the past, populating table rows was done like this:
INSERT INTO Foo VALUES (1, 'Books');
INSERT INTO Foo VALUES (2, 'CDs');
INSERT INTO Foo VALUES (3, 'DVDs');

-- or

INSERT INTO Foo (keycol, datacol)
SELECT 1, 'Books'
UNION ALL
SELECT 2, 'CDs'
UNION ALL
SELECT 3, 'DVDs';

-- or using on the fly

SELECT keycol, datacol
FROM ( SELECT 1, 'Books'
UNION ALL
SELECT 2, 'CDs'
UNION ALL
SELECT 3, 'DVDs') AS Foo (keycol, datacol);

Here is how the same can be done with SQL Server 2008 table value constructors:
INSERT INTO Foo (keycol, datacol)
VALUES (1, 'Books'), (2, 'CDs'), (3, 'DVDs');

-- or using on the fly

SELECT keycol, datacol
FROM ( VALUES (1, 'Books'),
(2, 'CDs'),
(3, 'DVDs') ) AS Foo (keycol, datacol);

-- and CTE version

WITH Foo (keycol, datacol)
AS( SELECT *
FROM ( VALUES (1, 'Books'),
(2, 'CDs'),
(3, 'DVDs') ) AS F (keycol, datacol))
SELECT keycol, datacol
FROM Foo;

Another interesting option is to derive a row value from a subquery, like this:
INSERT INTO Foo (keycol, datacol)
VALUES ((SELECT MAX(keycol) + 1 FROM Foo), 'Tapes');


The big advantage of TVCs is that you save typing 🙂