The Transact-SQL script in this post returns a list of tables and a count of the number of records within each table.
This category contains useful information relating to Transact SQL (TSQL).
The code contained in this post can be used to find all tables that contain columns that have “ADDRESS” in the name. It can easily be modified to search for table column names with any specific word.
I have been using a Transact-SQL query (see Method 1 – Update entire table below) to get the Median “Order Value” for an entire list of Customers. However, due to the quantity of data, it was taking for ever.
Therefore, this morning I decided to write a SQL Server User Defined Function (see Method 2 – Function below) [1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.] to update the Median Order Value for a single Customer. I then used a UPDATE query to update all of the customers. This took a lot less time to process 🙂
The SQL Server function in this post can be used to highlight text (while preserving the case) that has been searched for within a web page.
The Transact-SQL script in this post which was written for SQL Server 2005, returns a list of the values and a count for each and every field within a table. You just need to change the schema name and table name, and specify a limit (if needed) for the number of values returned for each field.
I have just found a very useful article from SQLServerCentral.com, that goes through the process of replacing multiple spaces with one. “Replacing multiple spaces with a single space is an old problem that people use loops, functions, and/or Tally tables for. Here’s a set based method for replacing multiple spaces from MVP Jeff Moden” http://www.sqlservercentral.com/articles/T-SQL/68378/
The script in this post can be used to extract a list of databases along with the files associated with each database. The size (according to SQL Server) is also returned in pages and bytes.
Useful script to generate a list of tables for all the databases on a server
The query in this post return a list of Table Name, Column Name, and Column ID for all Primary Key columns within a database
Use this query (in this post) to produce a list of foreign key relationships, including the referencing and referenced table and column
The Transact-SQL query in this post returns the table name, column name, data type, and maximum length. This is useful when producing database documentation
This script contained within this post, applies permissions to all stored procedures or tables within the database.