In SQL 7 you had no way to convert local time to GMT (UTC time) which was added in SQL 2K as GETUTCDATE() which will output the current date you would get with GETDATE() but a it's GMT counterpart. Although this was a great improvement I work with data on another server that's front end […]
2002-02-05
635 reads
T-SQL analog for Visual Basic FORMAT function. Created for MSSQL 2000. Works regardless of language settins on server or client side! fn_format('YYYY/DD/MM HH:MI','2002-03-13 12:00') = '2002/13/03 12:00' fn_format('DD.MM.YY','2002-03-13 12:00:00') = '13.03.02' fn_format('HH:MI:SS','2002-03-13 12:00:00') = '12:00:00'
2002-02-05
982 reads
There is a script for setting up a blackbox trace on SQL 2000 described by Kalen Delaney in "Inside SQL Server 2000". Since I can never remember where to look it up I wrote this simple stored procedure to run the blackbox trace when I need it. This should be used with caution as it […]
2002-02-05
636 reads
This function can convert string with separated values to table. Exclusive feature: items inside quotes will not be splitted! Now you can easily perform joins on CSV strings! For expample: fn_split('1, 2, ''3, 4'', 5',',')= 1 2 3, 4 5 First parameter - string with values, second - delimiter character.
2002-02-05
1,189 reads
I find phone numbers to be the messiest data that users enter. There are so many variations with brackets that I wrote this UDF to help clean up the data. Usage: SELECT Firstname , SURNAME , udfStripBracket(PHONENUMBER) AS BracketlessNumber FROM dbo.UserContact
2002-02-05
274 reads
The procedure looks at the table you want to be audited and writes the INS, UPD, and DEL triggers automatically required for the auditing to work. The auditing requires a table called Progress to hold the audit information in so have provided the DDL for this as well. Typically you would write a cursor to […]
2002-02-04
493 reads
Searches SysComments for occurances of entered keywords and returns a list of procedures that contain the keywords. eg1) To find all procedures with 'nMyColumn' in EXEC utl_ProcSearch 'nMyColumn' eg2) To find all procedures with 'nMyColumn' in and 'nMyColumn2' in EXEC utl_ProcSearch 'nMyColumn', 'nMyColumn2' eg3) To find all procedures with temporary tables in... EXEC utl_ProcSearch '#' […]
2002-02-04
210 reads
It calculates log free space in mb and in percent for all databases, and also show the quantity of different objects for one databases.
2002-02-02
2,777 reads
This stored procedure reads the file names from a directory and stores just the names in a given table. It returns also the number of files present. The sp uses xp_cmdshell and so permissions are restricted to sysadmins and SQLAgentCmdExec. You can test with the following: Create Table ##tmp2 ( fldx nvarchar(255) ) Declare @FilePath […]
2002-02-01
1,981 reads
I wanted to be able to use info about an operating system file in TSQL. So I wrote this stored procedure that uses the special stored procedures xp_fileexist and xp_getfiledetails. This can tell if a file or folder exists, and if it is a file what is its size, created date and time, last written […]
2002-02-01
1,273 reads
Forgive me for the title. Mentally I’m 12. When I started my current day...
By Steve Jones
One of the things a customer asked recently about Redgate Data Modeler was how...
By Steve Jones
For a number of years, we’ve produced the State of the Database Landscape report,...
Hi all, I've just had to roll back my SSMS 22 version from 22.3.0,...
Hi! I've been banging my head against the wall for 2 days now trying...
Comments posted to this topic are about the item The Power of Data and...
In SQL Server 2025, there is a new function that returns the current date without the time. What is it?
See possible answers