Transact-SQL

This category contains useful information relating to Transact SQL (TSQL).

Table information – column lengths, counts and unique counts

1
1 month ago
in
Hi. The Transact-SQL script below returns the following information: ID – Just an identity column used within the script SchemaName – The name of the schema TableName – Obviously, the name of the table ColumnName Type – The data type of the column Length – The maximum number of bytes used by the [...]

Return the MAX of all dates within all tables within all databases

0
10 months ago
in ,
I wanted to produce a list of the MAX of date fields (DateTime, SmallDateTime, TimeStamp) for all tables within all databases held in an instance of SQL Server. The script below was written for SQL Server 2000, but should be easily customisable for SQL Server 2005 As always, please ensure you test [...]

Top most used query plans that are cached by SQL Server for faster query execution

0
1 year ago
in
The query below, returns the top 20 most used query plans that are cached by SQL Server for faster query execution. For more information regarding the dynamic management view, please see “sys.dm_exec_cached_plans (Transact-SQL)” in Books Online. SELECT TOP 20     CASE cp.objtype         WHEN [...]

Do you want to execute a Stored Procedure as part of a SELECT statement??

0
1 year ago
in
I was on a training course at the end of September, and came across a way to execute a simple stored procedure as part of a select statement using an Inline Table Valued Function. You (like me) have probably tried to run the following statement expecting to get results from a stored procedure: USE [...]

List of tables and indexes, with associated File Group

0
1 year ago
in
The script below (written using SQL Server 2005), returns a list of all tables (along with the schema name), and indexes (where they exist), and the name of the associated file group. This will useful to find out which objects have been assigned to the wrong file group. I have a database with over [...]

Search all table columns for specific text

0
1 year ago
in
I needed to find out which columns within all tables in the database, contained a specific string of text. I therefore wrote the script below to do exactly that CREATE TABLE TempTableColumnResults ( TableName VARCHAR(100),   ColumnName VARCHAR(100) ) DECLARE @TableName VARCHAR(100) DECLARE [...]

Sort results based on a Stored Procedure parameter

0
1 year ago
in
If you want to be able to specify the sort criteria for the results returned from a stored procedure, based on a provided parameter, you might want to try using the QUOTENAME function. Example: Firstly, here is the declaration (it can easily be changed into a stored procedure): DECLARE @OrderBy [...]
Go to Top