How to find the data file location of the Database ?

While creating the database the two types of data files such as MDF & LDF will be created in the drive.

If we want to see the data file location of the particular database we can find the location via T-SQL query.

SYNTAX

SP_HELPFILE

EXAMPLE

I just executed the below query to find the location of the adventure works database in my instance.

The above command will return the name of the data files , location and its filegroup etc as shown above.

You can easily identify the location using the above command in your instance.

How to find the list of index in a table ?

SQL server provide system stored procedure to find the list of indexes in the Tables (or) View.

Syntax 

SP_HELPINDEX 'Object Name'

Example

The following example list the index name,description and its keys for the Employee table.

How to get the definition of objects in SQL server ?

 Following options are used to get the definition of objects in SQL server.

TABLE:

SYNTAX:

SP_HELP [TABLE NAME]

EXAMPLE:

SP_HELP StudentDetail

In above example we can find some of the table details in Results tab.

Name --> Refers to table Name
Created Date--> Refers to table created date
Column Name --> Refers the column names in table
Type --> Refers to Data type
Computed --> Refers to the computed column
Length --> Refers the Column length
Nullable --> Refers to nullable column or not
Collation --> Refers to the column collation
Identity --> Refers to the identity column
RowGuidCol --> Refers to the RowGuidCol column
Index Name --> Refers to the Index Name for the index column
Index key --> Refers to the index column name
Constraint type --> Refers to the constraint type such as primary key constraint ,foreign key constraint,default constraint etc.

Database Objects:

SYNTAX:

SP_HELPTEXT [Database Objects]

EXAMPLE:

SP_HELPTEXT pGetStudentDetail

Database objects such as stored procedures ,functions ,triggers and views.
For all objects (except tables) we can use the above syntax to get the definition.

How to Change the Default Database in SQL Server ?

While opening a new query window,Initially the selected database will be Master 

To change the Default database in SQL server while login use the following syntax.

Syntax : 

EXEC SP_DEFAULTDB 'LOGIN NAME','DATABASE NAME'

Example :

EXEC SP_DEFAULTDB 'SA','SAMPLE'

Login Name -- Refers to the SQL server login Name

Database Name -- Refers to the default database Name

After executing the above system stored procedure ,the default database will be changed from master database to Sample database.

In next login the default selected database will be Sample.

How to rename the existing table in SQL server?

After the table creation ,it is also possible to change the table name in SQL server.

Syntax : 

SP_RENAME '[EXISTING TABLE NAME]','[NEW TABLE NAME]'

Example :

SP_RENAME 'Student','StudentDetails'

Now the table name will be changed from Student to StudentDetails in the executed Database.

How to rename the existing column in SQL server?

After the Column creation ,it is also possible to change the column name in SQL server.

Syntax : 

SP_RENAME '[TABLE NAME].[EXISTING COLUMN NAME]','[NEW COLUMN NAME]','COLUMN'

Example :

SP_RENAME 'Student.StudentAddress','StudentAdd','Column'

Now the Column name will be changed from StudentAddress to StudentAdd in Student table.