This is a quick and dirty stored procedure that one can use to determine if a column or set of columns is a candidate for a primary key or unique index. The procedure takes a table name and a concatenated list of columns to be checked. Non-char columns must be CAST as VARCHAR.
I don’t remember who wrote the original code for this stored procedure; however, I found it useful enough I wanted to share it with my readers.
The following script does a fantastic job of allowing the user to defragment table indexes with a high degree of control. I have tested the procedure with SQL Server 2008 and 2008 R2. Your mileage on other versions of SQL Server may vary.
I needed to find a simple way to implement a text search on a table without using MySQL’s fulltext search functionality. I am currently limited to using a version of MySQL that only supports fulltext search on the MyISAM storage engine. My tables use the InnoDB storage engine, hence the problem. This post will show a quick and dirty way to search multiple database fields with multiple search terms using only SQL.
The Dynamic Data System (DDS) makes loading a data warehouse much easier and faster. This system uses SSIS to load standard and partitioned tables via SSIS packages created on the fly using metadata. This eliminates the need to maintain complex SSIS packages. Only basic T-SQL skills are needed. The Dynamic Data System is available on CodePlex.
My current data warehouse project uses SQL Server 2008 R2. Out of the box I found a couple minor configuration issues that prevented me from getting started with using DDS. The following notes, in addition to the documentation provided with the product, should get you on your way.
Continue reading “Tips for Running the Dynamic Data System (DDS)”
Here is a very simple SSIS script task to convert a single file from one code page to another. We will be converting any file that is UTF-8 to ANSI 1252. The original file will be replaced with the converted file. Continue reading “SSIS: Simple File Encoding Converter Script Task”
I have included a quick script that creates a stored procedure that will generate a data dictionary on the fly for any SQL Server 2005 or 2008 database: Continue reading “Create a Data Dictionary on the fly for any SQL Server 2005 or 2008 Database”
Presented is a script that allows one to import all XML files in a specific directory into SQL Server 2005 or 2008 and store it as XML. Most of what is presented should work for importing the contents of any type of file, not just XML. The example uses OLE Automation Procedures to extract extra data that one may find of use storing along with the XML such as file creation time, size, etc. Continue reading “Import XML Files into SQL Server 2005 or 2008”
Here is a short example of a recursive stored procedure. It returns the nth piece of a delimited text string using recursion. There are better performing methods for doing this type of task, however, I found it an interesting exercise none the less.