SQL Server Development Techniques and Scripts



Donate to support site
Consultancy - System design advice, mentor staff, carry out bespoke work
Contact consultancy @ elmcrestprogramming.ltd.uk
Site Forum, email me or discuss here

Transact SQL
Triggers
Administration
Articles I have authored on other sites
Links - Useful sites about sql server and others



Transact SQL
sp_executeSQL setting variables from dynamic sql
Retrieve Tree Hierarchy Retrieve formatted tree structure
Get table row counts Retrieve the number of rows in each table in a database
Find gaps in sequence numbers Find gaps in sequence numbers
Create text file Creating a text file from a stored procedure
spFormatOutputBuffer Retrieve a sql server error message
Access Temp Tables Across SPs Create a temp table in one SP and access from another
sp_CreateDataLoadScript Create a data insert script from a table
sp_CreateDataLoadScript(remote) Create a data insert script from a table on a remote server
Create Script File Concatenate files to make single script
Move data using column definitions Move data from import table to production table using file format definition
Remove non-numeric characters Remove non-Numeric or non-alphameric characters from a string or field
Find non-alphameric characters Function to return all non-alphameric characters from a string in a table
Primary Key Columns Get all fields that are part of the primary key
Check if file exists find file - scripting object, xp_cmdshell, xp_fileexist
Cursors Advanced use of cursors in t-sql
Import Text Files Import and archive text files that arrive in a directory
f_GetEntryDelimiitted Get entries from csv string
fn_ParseCSVString Function to return a table from a delimitted (csv) string
bcp bcp using format file & quote delimitted strings
CSV String From Table Create delimitted string from table entries
BCP all tables BCP in and out data from all tables in a database
CrossTabs Crosstabs and pivot tables
Pivot_Statement Pivot_Statement
s_ProcessAllFilesInDir Process all files in a directory
UpdateText Inserting text data to a table in 8000 byte chunks
Replace Text Search and place strings in a text column in a table
Replace Text (2) As above - allows for replaced string in replacing tsring
Table name as variable Accessing a table from a name in a variable
send email Send emails asynchronously
Import and parse XML Import and parse XML document file using tsql
In csv string parameter Pass csv string parameter to an "in" statement
sp_ExecLinkedServer Run a query on a remote server (e.g. Oracle)
Calculate Average Duration Calculate average duration, date arithmetic
csv string from rows Create a separated list string from table row values
Date processing Format a date for input and ouput
Daily time between events Daily shift time, break time, number events
Daily Hours Worked Daily/weekly hours worked from start and end shift times
Regression Testing Compare before and after versions of tables after a system change
Maintain type2 dimension Update type 2 dimension from input data
Generate Test Table Data Populate a table with weighted random data entries
Generate insert statement Generate insert statement from table structure

Triggers (under development)
Triggers 1 A beginners guide
Triggers 2 Creating Audit Trails
Generate Trigger Generate a trigger from the table structure to log field updates by field name
Triggers Basic trigger information
Audit trail trigger Audit trail for all fields in a table from the table structure
Columns_updated() Trigger to detect columns updated - more than 32 fields
View audit field changes Return before and after values of rows in which fields have been changed on a particular day

Administration
Detecting installed sql server version Detecting installed sql server version
connection network library Set connection network library in registry
Spaceused for all tables Get space used (sp_spaceused) for all tables in a database
Backup a database T=SQL code to backup A database, Full, Log and differential
Backup all databases on a server Stored procedure to backup all databases on a server
sp_nrinfo Displaying blocking and connection info
sp_nrSpidByStatus Displaying connection commands by status
sp_nrLocks display commands executed and locks held by spids
Page Structure Data page structure and display using dbcc page
Recover corrupt database Recover data from a corupt database
Alter table - good or bad? Possible detrimental effects of using alter table
Large Tr Log File Shrinking a large transaction log file and stopping it from growing - part 1
s_TestRestore Automated test restore of latest backup with move of logical files
Add a self linked server Add the local server as a linked server under another name
Synchronise Directories Copy / synchronise directories / folders
Copy latest backup Copy most recent full backup to local server
Restore latest backup Restore most recent backup and move physical files
s_RestoreDatabase restore full backup and apply logs
Log Shipping Log Shipping
Move Databases Move physical location of database files - including system databases
Bad things Bad practices in sql server
Save Query Plans Save query plans and statements for all running spids
SQL Server 2008 New Functionality What's new in SQL Server 2008
Custom Keyboard Shortcuts Custom Keyboard Shortcuts, building dynamic sql

Articles
sql server 2005 CTEs sql server 2005 common-table expressions (CTEs)
sql server Performance Diagnosing Batch System Performance Issues
sql server 2005 ssis exec sql server 2005 executing ssis packages
sql server csv files Creating csv files using bcp and stored procedures/
sql server 2005 SSIS FTP file rename SSIS move/rename a file on an FTP site
Partitioned Tables in SQL Server 2005 Partitioned Tables
Identity Columns Identity Columns

Links - Useful sites about sql server and others
sqlteam.com Friendly, active, sql server question and answer forum + articles
www.simple-talk.com SQL Server/.net articles
sqlservercentral.com Another sql server question and answer forum
replicationanswers.com Articles about replication by Paul Ibison
tek-tips.com Question and answer forum for all technologies
sswug.org Useful sql server site
sqldts.com Everything you need about DTS
mysql.com mysql
comparewebhosts.com finding a web host
mindsdoor.net Software development, SQL, SSIS, Excel, Powershell etc.


Books Amazon.co.uk
cover cover cover