Free Essay

Senior Sql Server Dba

In: Other Topics

Submitted By kumarmsql
Words 650
Pages 3
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]…...

Similar Documents

Premium Essay

Cis 499 Sql Coding Senior Seminar

...SQL Coding Proj. Draft Building of Tables and Inserting Values PARENTS TABLE The building of the tables were done in SQL which is a program that the PL/SQL coding is done in that talks to the computer to let the computer know exactly what it needs to create for this particular table. The tables were created for formality purposes to keep the assistant from doing any documentation of parents and children on paper. This saves a lot of time and writing. As a child enrolls in the facility, there information and their parent(s) information is put into the system and created. Once created, I then entered the information that was called out in the creation of the tables. This information is shown below. SQL> CREATE TABLE PARENTS( 2 parent_id numeric(5), 3 gender_code varchar2(8), 4 marital_status_code varchar2(15), 5 first_name varchar2(25), 6 last_name varchar2(30), 7 address varchar2(35), 8 city varchar2(25), 9 state varchar2(2), 10 zipcode numeric(5), 11 email_address varchar2(35), 12 phone_number numeric(10)); Table created. SQL> INSERT INTO PARENTS (parent_id, gender_code, marital_status_code, first_name, last_name, addres s, city, state, zipcode, email_address, phone_number) 2 VALUES ('1100','Female','Single','Kim','Jones','453 Dayton Drive','Midfield','AL','35667','KJon es@yahoo.com','2056377454'); 1 row created. SQL> INSERT INTO PARENTS(parent_id, gender_code, marital_status_code, first_name,......

Words: 1287 - Pages: 6

Premium Essay

Sql Server Authentication

...SQL server authentication (1) SQL server recognizes two different types of authentication: Windows Authentication and SQL Server Authentication. Using Windows Authentication allows users to provide some kind of credentials associated with the windows user accounts built into the domain of your server if it is a windows based system. Using windows authentication provides a more secure environment and is considered integrated security. Each user has a domain log in or account name that is used for windows and then re-used for SQL authentication. The second method of authorization is simply SQL server authentication. This is typically used when there is no access to an active windows domain or an account does not exist for that user to authenticate through windows. You can use what is called a mixed mode authorization and use a combination of both windows and SQL authentication. However, this opens more access points to your database server and therefore less secure and not recommended. Using windows authentication is more secure because it allows for authentication at a domain level and can be managed more easily through active directory accounts and groups. http://sqlchicken.com/2009/09/sql-university-basic-tools/ (2) Microsoft Access has several authorization options. These include role-based, identity-based, and resource-based options. Role-based authorization is secured based on the role of the user. All users that fit in the same role......

Words: 304 - Pages: 2

Free Essay

Sql Server Authentication

...Windows Authentication on Microsoft SQL Server Introduction Microsoft SQL Server offers two types of security authentication: SQL Server authentication and Windows authentication. SQL Server authentication authenticates the user to the database using a database user name and password. Windows authentication is also referred to as "Windows Integrated Security" or a "trusted connection" because it relies on the user being authenticated, or “trusted,” by the operating system. Windows authentication is the authentication mode recommended by Microsoft. Windows authentication takes advantage of Windows user security and account mechanisms. By allowing Microsoft SQL Server to share the user name and password used for Windows, users with a valid Windows account can log into Microsoft SQL Server without supplying a user name and password. In addition to a single login within a Windows domain, Windows authentication provides a more secure mechanism for logging into Microsoft SQL Server. Standard Windows security mechanisms also provide the added advantages of auditing, password aging, minimum password length, and account lockout after multiple invalid login requests. The DataDirect Connect® for JDBC® SQL Server driver is the only JDBC driver for Microsoft SQL Server that provides two methods for supporting Windows authentication, a Pure Java (Type 4) implementation and a Windows-specific (Type 2) implementation. The Windows-specific implementation requires minimal configuration to......

Words: 2311 - Pages: 10

Premium Essay

Sql Server 2008 Week 1

... T 13. F 14. T 15. T 16. F 17. F 18. T 19. T 20. T 1. 3  Major Revisions and 11 minor. The latest SQL standard was adopted in July 2003 and is often called SQL:2003. 2. SQL Express SQL Server Express supports most of the features and functionality of SQL Server. The following table lists some of the major features and components that are supported. Stored Procedures | SQL Server Configuration Manager | Views | Replication (as a subscriber only) | Triggers | Advanced Query Optimizer | Cursors | SMO/RMO | sqlcmd and osql utilities | Integration with Visual Studio 2005 | Snapshot Isolation Levels | Service Broker (as a client only)¹ | Native XML support, including XQuery and XML Schemas | SQL CLR | Transact-SQL language support | Multiple Active Result Sets (MARS) | Dedicated Administrator Connection² | Import/Export Wizard | SQL Server Express supports Service Broker, but direct communication between two SQL Server Express servers is not supported. The Dedicated Administrator Connection feature for SQL Server Express is supported with the trace flag 7806. SQL Server Features Not Supported in SQL Server Express SQL Server features not supported in SQL Server Express | SQL Server features from previous versions not supported in SQL Server Express | Database mirroring | SQL Mail | Online restore | Fail-over clustering | Database snapshot | Distributed partitioned views | Parallel index operations |......

Words: 783 - Pages: 4

Free Essay

It- Sql Server 2012

...Course 10774A: Querying Microsoft SQL Server 2012 Length: 5 Days Prerequisites: Before attending this course, students must have: Working knowledge of relational databases. Basic knowledge of the Microsoft Windows operating system and its core functionality. Before attending this course, students should have: Basic understanding of virtualization technology (Classroom labs utilize virtual machines) To help you prepare for this class, review the following resources: Book: T-SQL Fundamentals for Microsoft SQL Server 2012 and SQL Azure MCTS Self Paced Training Kit 70-461 About this Course This 5-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. This course helps people prepare for exam 70-461. All the labs for this course can be performed using the provided virtual machines. However, if you have a Microsoft Windows Azure account and the classroom virtual machines connect to the internet you may be able to connect to your Windows Azure server and database from the classroom. Many of the labs in this course are enabled for you to perform the lab while connected to your own Windows Azure database in the cloud. Your instructor should be able to provide a current list of Windows Azure enabled labs. To acquire a trial Windows Azure account......

Words: 1666 - Pages: 7

Free Essay

Sql Server 2005

...SQL Server 2005基础教程 清华大学出版社 第1章 概述 教学目标 教学过程 2007年9月3日 第1页 SQL Server 2005基础教程 清华大学出版社 教学目标 理解使用数据库的原因 掌握数据库的基本概念和特点 理解Microsoft SQL Server简史 理解Microsoft SQL Server系统的体系结构 理解数据库和数据库对象的特点 理解管理工具的特点 理解数据库管理员的任务 2007年9月3日 第2页 SQL Server 2005基础教程 清华大学出版社 教学过程 1.1 为什么使用数据库? 1.2 什么是数据库? 1.3 Microsoft SQL Server简史 1.4 Microsoft SQL Server系统的体系结构 1.5 数据库和数据库对象的特点 1.6 管理工具 1.7 数据库管理员 2007年9月3日 第3页 SQL Server 2005基础教程 清华大学出版社 1.1 为什么使用数据库? 2007年9月3日 第4页 SQL Server 2005基础教程 清华大学出版社 1.2 什么是数据库? 数据库是采用计算机技术统一管理的相关数据的集合,数 据库能为各种用户共享,具有最小冗余度、数据之间联系 密切、有较高数据独立性等特点。 数据库管理系统(database management system, DBMS)是位于用户与操作系统之间的一层数据管理软 件,它为用户或应用程序提供访问数据库的方法,包括数 据库的建立、查询、更新以及各种数据库控制等。 数据库系统是实现有组织地、动态地存储大量关联数据、 方便多用户访问的计算机软件、硬件和数据资源组成的系 统,是采用数据库技术的计算机系统。 数据库产品是由专门开发DBMS的厂商提供的。 2007年9月3日 第5页 SQL Server 2005基础教程 清华大学出版社 1.3 Microsoft SQL Server简史 1987年,赛贝斯公司发布了Sybase SQL Server系统 1988年,微软公司参加赛贝斯SQL Server系统开发 1992年,联合开发Windows NT环境的SQL Server系 统 1993年,微软与赛贝斯公司联合开发正式结束 1995年,微软发布Microsoft SQL Server 6.0系统 1998年,微软推出Microsoft SQL Server 7.0系统。 2000年,微软发布Microsoft SQL Server 2000系统。 2005年,微软发布Microsoft SQL Server 2005系统。 2007年9月3日 第6页 SQL Server 2005基础教程 清华大学出版社 1.4 Microsoft SQL Server系统的 体系结构 2007年9月3日 第7页 SQL Server 2005基础教程 清华大学出版社 1.5 数据库和数据库对象的特点 数据库的类型和特点 数据库对象的类型和特点 2007年9月3日 第8页 SQL Server 2005基础教程 清华大学出版社 数据库的类型和特点 2007年9月3日 第9页 SQL Server......

Words: 7955 - Pages: 32

Premium Essay

Sql Server

...SQL Server 2012 Proposal for ABC Inc Name Institution Table of contents Cover page 1 Table of contents 2 Introduction 3 Statement of the problem 3 Solution 4 Conclusion 5 References 6 SQL Server 2012 Proposal for ABC Inc Introduction For an organization to carry out its processes effectively, it is important to have a database. Database management is essential in every organization. There are several methods that have existed over the years. They include excel spreadsheet and Access. These are good database, but I would like to propose a more advanced method. The SQL 2012 is a database system that will enable the organization to utilize technology (Mistry & Misner, 2012). The Microsoft SQL server 2012 consists of several editions that can help ABC Inc to meet its database requirements. Investing in this database system will be of many benefits to the company. Statement of the Problem In the world of changing technology, it is important to adopt change and advanced methods of running a company. Over the years, ABC Inc has been using excel spreadsheets and access; it is time to move to SQL 2012 server. It is not easy to store a lot of data in Excel and access compared to the SQL. The company has been able to use Excel over time, but it has not been possible to get high quality data.......

Words: 943 - Pages: 4

Free Essay

Using Sql Server in C Sharp

...  Telemark  University  College   Department  of  Electrical  Engineering,  Information  Technology  and  Cybernetics         Using  SQL  Server  in  C#   with  Examples   Hans-­‐Petter  Halvorsen,  2014.03.01       Faculty of Technology,   Postboks 203, Kjølnes ring 56, N-3901 Porsgrunn, Norway. Tel: +47 35 57 50 00 Fax: +47 35 57 54 01     Table  of  Contents   1.   Introduction  .......................................................................................................................  4   2.   SQL  Server  ..........................................................................................................................  5   2.1.   Database  Systems  .......................................................................................................  5   2.2.   Structured  Query  Language  ........................................................................................  5   3.   Visual  Studio  ......................................................................................................................  6   3.1.   The  .NET  Framework  ...................................................................................................  6   3.2.   The  C#  Language  ........................

Words: 1390 - Pages: 6

Premium Essay

Securing Sql Server

...Best practices for Disaster Recovery. Research Assignment 9 Robert Montini (18738519) Mr. Troianos Research Assignment 9 Robert Montini (18738519) Mr. Troianos Best practices for securing SQL Server. Best Practices for Disaster Recovery. Bad things happen, but to a corporation, entity or country, a bad thing happening to its server is worse than bad. It’s a Disaster. The loss of crucial information, records and vital statistics can bring the death to whatever the data base is associated to. That is why Disaster Recovery is one of the most prioritized tasks a data base team may face. Given that the creating a data base is in itself the major goal, protecting that data base should a disaster befall it is as important. There are numerous ways to do this. This paper deals with the best practices of how to accomplish this. The first thing should be to make a backup plan. This should: 1. A computer where the backup will be stored 2. What programs that will be used to back up the database 3, The computers to be backed up 4. A schedule of when to backup new data to the data base 5. The offsite location where the data base recovery data will be stored The second practice is to document all the changes that are made to the database. These include service packs, hot fixes and QFEs that have been applied. This is crucial for getting a data base restored to its original state should a disaster occur. These steps should be implemented to help prevent or minimize......

Words: 1274 - Pages: 6

Free Essay

Sql Server Security Best Practise

... SQL Server 2012 Security Best Practices - Operational and Administrative Tasks SQL Server White Paper Author: Bob Beauchemin, SQLskills Technical Reviewers: Darmadi Komo, Jack Richins, Devendra Tiwari Published: January 2012 Applies to: SQL Server 2012 and SQL Server 2014 Summary: Security is a crucial part of any mission-critical application. This paper describes best practices for setting up and maintaining security in SQL Server 2012. Copyright The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other......

Words: 15647 - Pages: 63

Premium Essay

Sql Server and Fragmentation

...SQL Server and fragmentation Just wanted to share a quick tip on querying on sql server fragmentation and fixing things if needed. On our production server, which was setup by our sysadmin with all defaults as per configuration. I was also told that there is a weekly job setup for rebuilding indexes if there is a fragmentation. I did accepted everything told at a face value. But now its time for some treasure hunting. I had to do some sql BOL searching as it was not clear to me how/where I could find such stats. Finally came across sys.db_dm_index_physical_stats function which needs several arguments and based on it it will return details about an object's fragmentation. Lets look at this function first. sys.dm_db_index_physical_stats ( DB_ID, OBJECT_ID, INDEX_ID, PARTITION_NO, MODE); Here db_id, and object_id can take  [ null | 0 | default ] as a value and all mean same. To narrow down the results, you need to supply actual value. Index_id could be [ null | -1 |  default ],  and all mean same, alternatively you can provide 0 if object is a heap, or actual id of an index if object is an index. Partition_no is the partition id of index or heap. Alternatively it can be one of [ null | 0 | default ] as a value and all mean same. Mode is a scan level used to gather the information. It can be any of [ default | null | limited | sampled | detailed ], where default (null ) is limited. So to begin with I took one of our biggest table and made the query...

Words: 409 - Pages: 2

Free Essay

Senior Database Administrator

...administrator certified expert.  Pass Oracle enterprise linux fundmental exam as a prerequisite for oracle enterprise linux system administrator certified associate.  Pass ITILV3 Foundation exam.  Oracle DB 10g: Administering Real application clusters certified expert.  Oracle DB: SQL Certified Expert. Professional Experience From To Title& Duties May 2011 Present @Umniah Senior database administrator Duties: Administration on Oracle E-business suite release R11.5.10.1 - Support/Maintain/Troubleshoot Umniah systems environments to be available 99.5% - Install, Configure, and Troubleshoot Oracle Applications and databases - Apply patches, fixes and work with Oracle support through SRs. - Use of Oracle Application Manager for EBS administration. - Cloning Oracle Applications using rapid clone -Upgrade EBS R 11.5.10.1 to R11.5.10.2 -Upgrade DB from 9.2.0.7 to 9.2.0.8 -Configure EBS application to work with JRE instead of Jinitiator. -Currently planning for upgrading EBS DB from 9.2.0.8 to 11.1.0.7 and EBS Application to R12. - Storage consolidation and data archiving. - Upgrade oracle DB from 9i to 11g. From To Title& Duties Sep 2010 May 2011 @Optimiza Oracle Apps DBA Duties: Administration on Oracle E-business suite release R12.1.1 - Support/Maintain/Troubleshoot greater Amman municipality EBS environments. - Install, Configure, and Troubleshoot Oracle Applications, including EBS and SOA. - Apply patches, fixes and work......

Words: 1191 - Pages: 5

Premium Essay

Microsoft Access or Microsoft Sql Server: What's Right in Your Organization

...Microsoft Access or Microsoft SQL Server: What's Right in Your Organization? SQL Server Technical Article Writers: Luke Chung Technical Reviewer: Matt Nunn Published: December 2004, revised July 2006. Applies To: SQL Server 2005 Summary: This paper explains how Microsoft® Access is used within an organization. It also explains when to use Access and when to use Microsoft SQL Server™. Copyright The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights......

Words: 6412 - Pages: 26

Free Essay

Performance-Counter-for-Sql-Server

...Few of my friends who wish to do a performance tuning in their servers, they asked me what are the counters that they should check that gave me an idea to prepare this document and publish across. Disk Related Counters: Object Name: Physical Disk Counters: Current Queue Length: Sustained high queue for a period of time means that the IO subsystem has a problem. Average Queue Length: If the Average Queue Length for the disk is high more than 2. Then this indicates a potential IO subsystem problem. This would also make the disk sec/read and disk sec/write high. Disk Sec/Read: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem. Under these conditions, consider the following Move the files to additional drives. Check for proper indexing on the tables. Check for the CPU and memory counters to find any other potential bottlenecks. Disk Sec/Writes: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem. Under these conditions, consider the following Move the files to additional drives. Check for proper indexing on the tables. Check for the CPU and memory counters to find any other potential bottlenecks. Placing transactional log files in separate drives to remove additional pressure on the drives Memory/Cache: Object Name: Memory: Counters: Page Faults/Sec: Page faults occur......

Words: 1126 - Pages: 5

Free Essay

Sql Server Tools

...SQL Server 2008 Tools Tool Windows Facilitate the development and modification of database objects, as well as effective management of SQL server. Object Explorer * Explore database objects on a server. * It is a tool used to initiate most database mgt tasks. * It is arranged in a standard tree view with different groups of objects nested in folders * There is ability to create scripts based on actions performed in the graphical designs because of SQL server mgt studio is exposed through object explorer. Code Editor * It provides the ability to open, edit, or create new queries. Types of queries supported by the editor are- * Db engine queries * Analysis Services MDX Queries * Analysis Services DMX Queries * Analysis Services XMLA Queries * SQL Server Compact It is essentially a word processor. It provides color coding of syntax, multiple query windows, and partial code execution when you highlight the desired code and click on the execute button or press. Solution Explorer Microsoft SQL Server Management Studio provides two containers for managing database projects such as scripts, queries, data connections, and files: solutions and projects.  * A project is a set of files, plus related metadata such as connection information. The files in a project depend on which Microsoft SQL Server component the project is for. For example, a SQL Server project may contain data definition language (DDL) queries...

Words: 1464 - Pages: 6