Using a temprary table as an array
This script is used to extract from a temprary table with long string all elements in an array formatlength of 15 chars is assumed.
2002-08-01
632 reads
Scripts
Smooshing String Function
Smooshing - The act of removing all non-alphanumeric characters from a string. We have found several areas of our application where we need to perform cleaning on strings. The following is a SQL function which can be used to remove any non-alphanumeric characters. To use the function create the script and then SELECT dbo.fn_SmooshIt('B?\RU.,C>}E') will […]
2002-07-26
557 reads
Automate Profiler Trace with xp_trace_
Instead of interactively running profiler, you can use built in stored procedures to obtain the same results. You can even schedule the execution of the trace and output to a designated output file.
2002-07-25
715 reads
Get the Foreign Key Hierarchy
This script will run through the foreign keys on tables to produce a hierarchy of the tables in a database.This is useful when you need to purge and repopulate test databases with foreign key contraints
2002-07-24
2,131 reads
Retrieve column attributes
Given database, owner, table, and column names, this procedure returns one from among several column attributes such as type definition, defaults, and other types of constraints. E.g., to retrieve the type definition of the 'pubs' database's 'author.address':DECLARE @attrib VARCHAR (500) , @msg VARCHAR (8000)EXEC p_get_column_attribute 'TYPEDEF' […]
2002-07-24
140 reads
Count # occurrences of one string inside another
Scalar function 'f_contains' counts the number of times the first argument occurs within the second argument.Given: @arg1, @arg2 (both VARCHAR), @cnt INTSELECT @cnt = dbo.f_contains (@f_search_for = @arg1 , @f_container = @arg2)
2002-07-22
394 reads
Slice out string from within specified delimiters
Scalar function 'f_delimited' slices out of a passed-in string, from a specified position inside the string, that segment of it which is delimited on one or both sides by a specified delimiter.Example: Print out a segment of a 'syscomments.text' column for a stored procedure 'p_proc', representing a line of code (i.e., the segment of 'syscomments.text' […]
2002-07-22
124 reads
String-search on objects in a specified database
Procedure 'p_find_string' allows users to specify any database on a server in searching for a specified character string. Supported for string-searching: table columns, view columns, trigger, function, and procedure code. Produces a report showing: object type, owner, object name, column id (for tables and views) or what line number (for triggers, functions, and procedures), and […]
2002-07-22
148 reads
Using UDFs to avoid cursors
Let's say you have 2 tables: a Contacts table and a PhoneNumbers table (which stores all the phone numbers for each contact). You are asked to write a query that returns each contact's name and a comma-delimited string of phone numbers for each contact, something like this:FirstName LastName PhoneNumbers--------- -------- --------------------------John […]
2002-07-22
1,044 reads
Get only the ODD record values from a set
Using a Having clause I eliminate getting the EVEN rows and getting ovly the odd primary keysBy using Having (count(*) %2) = 0 I will get the even and not the odd.
2002-07-20
310 reads
Blogs
Where Is My SQL Agent? Running Scheduled Jobs Against Azure SQL Database
By gbargsley
One of the first things I review when I inherit a new SQL Server...
AgentDBA vs Critical SQL Server
By Arun Sirpal
It’s 07:43. Someone’s already left a message. “Something’s wrong with the DB server.” You...
Finally Cleaning Up Dropbox: Moving Almost a TB to Google Drive with rclone
By davebem
I’ve had a Dropbox account for years. Like a lot of people, I started...
Forums
identity increments by 10,000 when it was supposed to be 1
hi a peer of mine who ive never known to be wrong says a...
Displaying Money
Comments posted to this topic are about the item Displaying Money
The Slow Growing Problems
Comments posted to this topic are about the item The Slow Growing Problems
Question of the Day
Displaying Money
I want to get the currency sign displayed with my amount stored in a money type. Does this work?
DECLARE @Amount MONEY; SET @Amount = '?1500'; SELECT CAST( @Amount AS VARCHAR(30)) AS EurosSee possible answers