You may be a tidy person who likes to keep naming standards for everything in SQL Server. It is a good practice since it makes generating scripts for everyday DBA tasks a lot simpler. I make all database files follow the following standard:DBName_D1.mdf for primary filegroup dataDBName_I1.ndf for index filegroupDBName_L1.ldf for transaction […]
2003-01-22
300 reads
This isn't a "script" per se - but rather an observation about a common issue in writing scripts.I want to alert SQL programmers to the issues surrounding the use of single-line comments. I try to avoid these whenever possible, and use block comments instead (except, of course, when commenting out the keyword GO).The reason is […]
2003-01-21
285 reads
Unlike shrinking transaction log, shrinking a data file seems to be tough. A lot of people complained about the "shrink database" option in EM not working, or DBCC SHRINKDATABASE not working, or DBCC SHRINKFILE not working. I have found that a data file will eventually shrink given enough try, time and patience. I've put everything […]
2003-01-14
414 reads
2003-01-14
91 reads
This UDF returns the Service Pack versionprint dbo.ServicePackversion()
2003-01-14
112 reads
I wrote this SP to be run from a job every night. Change the myDB reference to be a database on your system for DBA use.I just set up a job to run:Exec sp_CatchFileChangesThen I run in another step:Select * from mydb.dbo.tbl_sysaltfiles_3and output to a log file.Alternatively you could insert into another table with a […]
2003-01-13
306 reads
If new objects are created in the model database then these new objects only get created for new databases.Similarly, if objects are removed from user databases then getting them back into the database can be a pain.The following two stored procs copy objects from model to the current database if they do not already exist.
2003-01-10
44 reads
I wrote this sp to be able to calculate the distance between two locations on the earth if the latitude and longitude is known for each. Could be useful for GPS work or maps.Usage:If I know the following city locations:Sydney 151.2 E 33.87 SCanberra 149.15 E […]
2003-01-09
792 reads
WARNING: This is resource intensive. Don't use except as a last resort.I wrote this to be able to find if a TSQL batch is running already on a SQL Server. I use this rarely when I have to know to prevent running something twice.Usage:DECLARE @RC intDECLARE @SQLBatch nvarchar(255)-- Set parameter valuesEXEC @RC = [master].[dbo].[sp_RunningCheck] @SQLBatch […]
2003-01-09
414 reads
I wrote this function to work with my sp_EarthDistance stored procedure.It converts the regular degrees/minutes/seconds representation of angles to a decimal number.Remember to use '' instead of ' within a string to be treated a character and not an end quote.Sydney location 151¦12'0 "E 33¦52'0 "S and in […]
2003-01-09
833 reads
By James Serra
What problem is Fabric Ontology trying to solve? For years, most data conversations have...
By Steve Jones
Recently I ran across some code that used a lot of QUOTENAME() calls. A...
By ChrisJenkins
There are some telltale signs that your growing business has outgrown Excel for your...
Comments posted to this topic are about the item Stairway to Reliable Database Deployment...
Comments posted to this topic are about the item QUOTENAME Quote Parameters
Comments posted to this topic are about the item Limit the Blast Radius
When I use QUOTENAME(), I can optionally provide the character used to surround the string in the result. Can I use any character?
See possible answers