IMP Stored Procedures that Every DBA should know

-

This blog is the continuation of blog IMP DMVs, SPs & Functions that Every DBA should know.

In this blog, we will talk about some of the IMP and mostly used system stored procedures that every DBA should be aware of.

 

1) sp_helptext

Type: Stored Procedure

Description: This System Procedure is used to view the definition of database objects. (i.e., SPs, Views, Triggers)

Ex:

sp_helptext 'sp_helpDB'

Parameter_name

Type

Length

@objname

nvarchar

1552

@columnname

sysname

256

 

2) sp_helpdb

Type: Stored Procedure

Description: This SP returns information about specified database or all databases.

Ex:

sp_helpdb

sp_helpdb <Database_Name>

Parameter_name

Type

Length

@dbname

sysname

256

 

3) sp_helpfile

Type: Stored Procedure

Description: This SP returns information about specified database file or all database files.

Ex:

sp_helpfile

sp_helpfile <Filename> 

Parameter_name

Type

Length

@filename

sysname

256

 

4) sp_helpfilegroup

Type: Stored Procedure

Description: This SP returns the names and attributes of filegroups associated with the current database.

Ex:

sp_helpfilegroup

Parameter_name

Type

Length

@filegroupname

sysname

256

  

5) sp_helpstats

Type: Stored Procedure

Description: This SP Returns statistics information about columns and indexes on the specified table.

Ex:

sp_helpstats '<Object_Name>','<Result>' 

sp_helpstats 'PK__Employee__7AD04FF1CA043119','All' 

sp_helpstats 'dbo.Suppliers','All'

Parameter_name

Type

Length

@objname

nvarchar

1552

@results

nvarchar

10

  

6) sp_columns

Type: Stored Procedure

Description: This SP returns column information for the specified objects that can be queried in the current environment.

Ex:

sp_columns 'Suppliers'

Parameter_name

Type

Length

@table_name

nvarchar

768

@table_owner

nvarchar

768

@table_qualifier

sysname

256

@column_name

nvarchar

768

@ODBCVer

int

4

 

Comments

Post a Comment

Popular posts from this blog

What is DACPAC and how to use it?

-
Image
Few days before someone asked me a question about DACPAC and BACPAC. To be honest I was really not aware of it and I replied that I have heard these terms for the first time. That person replied DACPAC is used for deploying and upgrading database objects. Even after the clear statement, my reply was like developers would answer this better. Even though DBAs are responsible for migration and deployments. After few days, I got a requirement to migrate database schema (Only Schema) to another instance. In such a case most of us would have used the traditional way to script out the database objects and run the script on another instance. But what if there are many objects? I was exploring the easiest way to migrate the objects and got to know about DAC. After reading about DAC, I felt very sorry myself that I was not aware of such a simplest way of migrating and deploying database objects. I went to my friends who are senior developers and asked this question. None of them was aware ...
Read more

Always on setup end to end - Part 1 || Pre-requisites

-
Image
Hello readers,  Welcome to the new blog after a long time. In this blog I would like to discuss complete end to end always on setup guide for beginners. I will be posting couple of blogs regarding always on in SQL server. I believe that would be helpful to understand Always on in SQL server in easy way. Always on setup involved multiple steps. Hence one may find it difficult. However, if you could plan it well then you may find that always on is much easier to configure and handle. So, without wasting the time lets proceed further. Firstly, we will see what is Always on in SQL Server ? Always on is the high availability and disaster recovery solution introduced in SQL server 2012. As the name suggest, Always on means availability of database all the time even in case of disaster. We can say it is the alternative of mirroring. Only thing is windows failover cluster service also required to be configured as a pre-requisite for always on. In this blog, we will only d...
Read more

Insufficient disk space in filegroup

-
Image
A few days before one of my DBA friend suggested to write a blog on database file growth issues and how to troubleshoot it.  I liked his suggestion as we DBAs often face space issues in production. In this blog, we will see what steps and precautions we can take if a database file growth issue is encountered.   To reproduce the issue below steps have been followed. 1) Created database “Growth” . 2) Added one filegroup named “Growth” and make it default.  3) Added one NDF named “Growth2.ndf” in the filegroup “Growth” and kept it on another drive. 4) Created Table named “employee_details” in filegroup “Growth” 5) Inserted data into the table and created multiple indexes with low fill factor till the datafile is not getting full.  Once the data file got full and there is no space available for new data, then the below error got triggered. The statement has been terminated. Msg 1101, Level 17, State 12, Line 10 Could not allocate a new page for database 'Growth' ...
Read more