Posts tagged Transact SQL

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 [...]

Count the number of records in each table within a database

0
1 year ago
in
The Transact-SQL script below (written for SQL Server 2000, but also works with 2005), returns a list of tables and a count of the number of records within each table. USE [database name] GO DECLARE @name VARCHAR(500) DECLARE @sql VARCHAR(1000) CREATE TABLE #TableRecordCount ( TableName [...]

Finding table column names with a specific word

0
1 year ago
in
The code below 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. SELECT     CASE O.xtype WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' END AS Type,     O.name AS TableName,     [...]

SQL function to highlight text that has been searched for

0
1 year ago
in
The SQL Server function below, can be used to highlight text (while preserving the case) that has been searched for within a web page. For example: Source: “This is the source text” Search: “source” Function call: SELECT dbo.fnHighlightSearchText('source', 'This is the source text') Return [...]

REPLACE Multiple Spaces with One

0
2 years ago
in
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 [...]

COALESCE – Return first non-NULL value from multiple choices

0
3 years ago
in
Whilst studying for my MCITP Database Developer certification, I have come across a very useful function that returns the value of the first non-NULL value from multiple choices: SELECT LastName, FirstName, COALESCE(MobilePhone, HomePhone) AS Phone If the MobilePhone field is blank, it will use the [...]

Create a stored procedure using a parameter default of NULL

0
3 years ago
in
The default value of a parameter can be specified as NULL. In this case, if you do not supply a parameter, SQL Server executes the stored procedure according to its other statements. No error message is displayed. The procedure definition can also specify that some other action be taken if you do [...]
Go to Top