I wanted to look for all non-standard (i.e. A-Z/a-z/0-9) characters in a field across all records in a table. Therefore, I have adjusted my previous script to do so: Simply change to the required database, and change the select statement in the cursor. Please always test on a development server/database before running on Production.
This category contains useful information relating to Transact SQL (TSQL).
New script to return the number of records in all tables in a specific list of databases.
I have only just found out about the “MERGE” transact-SQL statement that was introduced in SQL Server 2008.
“Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.”
Script to retrieve a list of databases with the associated recovery model. Useful for checking backups.
I have developed a script that can be used to archive offline databases. This script first brings the databases online, then backs them up, detaches them, and then moves the MDF and LDF files.
I have just improved the script in SQL: List of table columns, with data types:
It now includes an indicate for nullable fields, primary and foreign keys, unique and check constraints, and identity columns
Question asked by Pramod on my old Forum.
The example query below creates a product category table and populates it with sample data. I then use a CTE query to return the categories, along with the breadcrumb trail (lineage)
The script in this post can be used to obtain an example of each of the different styles that can be output using the CONVERT Transact-SQL statement when converting date/time to a string. I have output the styles including the century, but the script can easily be modified to output examples without.
If you need to analyse the number of email addresses by email domain, e.g. “@abc.com”, then the script within this post will return the number of records grouped by the domain name of each email address (i.e. to the right of the @ sign)
The script within this post, outputs a list of each DATETIME and SMALLDATETIME field within a database, along with the minimum and maximum values. Useful to identify tables that have a date created or updated column, and you want to find out if they are still being used.
Do you have any spurious characters in a field??
Well, the code within this post, can be used to obtain a list of the ASCII character codes for each individual character of a field