There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Tag: TSQL
Searching Stored SQL – Part 3
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Searching Stored SQL – Part 2
There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…
Searching Stored SQL – Part 1
Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…
Full-Text Search – Part 1
Today, I want to go over why text searching for patterns is an expensive query operation. I am going to create a [WILD LIFE] database that contains a table called [ANIMALS]. A identity column called [ID] will be populated by the system automatically and a [NAME] column will be loaded with 445 animals names that I grabbed from WIKIPEDIA. The idea is to search for animals that have a root word in common. The snippet below creates the database and table. The full SQL script including insert statements is at…
Command Shell
It is sometimes necessary to execute external commands in windows command shell from TSQL. The xp_cmdshell extended stored procedure allows you to do just that. Because this command shell can be a potential target for hackers, it is turned off by default. Today, I am going to use this command to solve a real world ETL problem. The following SQL snippet uses the sp_configure command to enable this option.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="color: #008000;">-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO </span> |
The xp_cmdshell command takes a valid DOS string as input and executes asynchronously in a windows command shell until the…
Import & Export Data Part 2
The BULK INSERT statement imports formatted data directly into a table or view of your choosing. The main advantage of this statement is that is minimally logged if the correct recovery model is choosen. Peforming a transaction log backup after each bulk insert reclaims the log space that was used. This statment has many parameters that can alter how the statement executes. Today, I am going to demonstrate the parameters that I think are most useful. We will be working again with the Boy Scouts of America (BSA) hypothetical database.…
Import & Export Data – Part 1
There are not many real life databases that do not import data from external sources or do not export data for delivery to external targets. I am going to devote a series of talks on different ways to perform these functions. The INSERT statement is the easiest way to load small sets of static information into a database. We will be working again with the Boy Scouts of America (BSA) hypothetical database. The first example loads the rank table with a data. We can see the auto-increment surrogote key is…
Temporary Tables – Part 5
Today, I am going to talking about common table expressions. A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single DML statement. A CTE is simular to a derived table in which the results are not stored as an object; However, CTE’s can be recursive or built upon other CTE’s. The WITH key word is used to indicate the start of a CTE and needs to be preceeded with a semicolon. The rest of the syntax is like a stored…
Temporary Tables – Part 4
Today, I am going to talk about regular tables in tempdb. Please check out the Microsoft white paper on working with tempdb for a detailed discussion. The advantage of creating a table in tempdb is that it is persistent until the server is rebooted. Upon startup of SQL Server, tempdb is created again from scratch. One use of this technique is to have startup procedures create tables in tempdb that are used by an application program. One can leverage the fact that operations within tempdb are minimally logged. The major…