Some useful SQL commands


Here are some SQL commands that we rarely use but are very useful in simulations when we want to see the objects that exist in our database. These include :

1. Get list tables in database :

SELECT * FROM sys.Tables

2. Get list Stored procedures in database :

SELECT * FROM sys.procedures;

3.  Get list User defined functions in database :

SELECT name AS function_name, SCHEMA_NAME(schema_id) AS schema_name, type_desc FROM sys.objects WHERE type_desc LIKE ‘%FUNCTION%’;

4. Get  constraints in database : 

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType FROM sys.objects WHERE type_desc LIKE ‘%CONSTRAINT’

5. Get detailed Stored procedure :

sp_helptext <Stored_Procedure_Name>

6. Execute a Stored procedure in SQL :

EXEC <StoredProcedure_Name>

If the stored procedure has spaces in its name, enclose it between double quotes

EXEC “<StoredProcedure_Name>”

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in Interview Questions, SQL Server and tagged , . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s