ColumnSearch – USER GUIDE
Version 1

Copyright Notice

The information in this manual is furnished for your use only and is subject to change without notice. Copying, modifying or distributing this document in violation of the license agreement is expressly prohibited.

About This Manual

Purpose

This manual is intended to be a guide and a reference manual to search a column on all the databases from a Server and find what databases and objects a column belongs to. It also shows the Column size.

Who Should Read This Manual

Anyone who is working on MS SQL Server 2000/2005 Databases can read this document like any Develoers, DBA’s, Testers, etc.

How Is It Organized

1. User needs to Compile/Run the script to create SP named ‘ColumnSearch’ in Master database of a Server. 2. After successful Compilation, user should execute the SP in Master database of a Server. 3. Users will get the result set in the form of Database name, ObjectName, ColumnName, and ColumnSize.

List of Abbreviations

|S No |Abbreviation |Expansion |
| |TCS |Tata Consultancy Services |
| |SP |Stored Procedures |

Contents

1. Introduction 7 1.1 Overview 7 1.2 Features 7 1.3 Access Privileges 7
2. Installation Procedure 8 2.1 Task 1 8 2.2 Task 2 9 2.3 Result Set 9
Appendix ..10

Revision History

Document Detail:

|Name |Version No. |Description |
|ColumnSearch – User Guide |1.0 |Describes how to use ColumnSearch SP |

|Action taken |Preceding |New Page No |Revision Description |
|(Add/Del/Chg.) |Page No | | |
| | | | |

This document and any revised pages are subject to document control. Please keep them up-to-date using the release notice from the distributor of the document.

Approved by: Date:

Authorized by: Date:

1. Introduction

2 Overview

ColumnSearch is an SP (Stored Procedure), which we can use to search a column on all the databases from a Server and Find what databases and objects a column belongs to; it also shows the size of Column. This SP should be executed from master database only.

3 Features

1. This SP will be helpful to Search any Columns from all the Databases & Database Objects. 2. Helpful to compare the Column Size of Columns from varies Database Objects.

4 Access Privileges

3.1 To Run Script to Create SP: User should be belongs to the group of db_owner or db_ddladmin fixed database roles or the sysadmin fixed server role.

3.2 To Execute SP: User requires Permissions of Exec
4. Installation Procedure

4.1 Task 1:

Run the following Script in Master Database of a Server to Create the ColumnSearch Stored Procedure with @columnnametosearch as an input parameter.

--For MS SQL Server 2000

Use master Go

Create Procedure ColumnSearch @columnnametosearch varchar(128)

As

Begin

Set quoted_identifier off Declare @query varchar(1000) Set @columnnametosearch =@columnnametosearch Set @query='set quoted_identifier off begin use [?] end begin Select db_name() as DatabaseName, object_name(id) as ObjectName, name as ColumnName, length as ColumnSize from syscolumns Where name ="'+@columnnametosearch+'" end'

Exec sp_MSforeachdb @query

End

---- For MS SQL Server 2005

Use master Go

Create Procedure ColumnSearch @columnnametosearch varchar(128)

As Begin

Set quoted_identifier off Declare @query varchar(1000) Set @columnnametosearch =@columnnametosearch Set @query='set quoted_identifier off begin use [?] end begin Select db_name() as DatabaseName, object_name(object_id) as ObjectName, name as ColumnName, length as ColumnSize from sys.columns Where name ="'+@columnnametosearch+'" end'

Exec sp_MSforeachdb @query

End

2. Task 2:

Execute the Stored Procedure ColumnSearch in Master Database of a Server and pass the Input parameter as a Column Name, which we need to search from the Databases & Database Objects.

Example:

Use Master

Execute ColumnSearch ‘Address’

-- Where ‘Address’ is a Column Name that we need to search.

3. Result Set:

We will get the result set in the form of

DatabaseName ObjectName ColumnName ColumnSize

Appendix

Example: Search Databases & Database objects for column ‘Address’ with Size of Column.

1. Create SP ColumnSearch [pic]

2. Execute SP ColumnSearch & see the Result Set for Column ‘Address’

[pic]