To retrieve metadata information about tables using Information_schema related views

List of the tables:
-----------------
The below query gives the information about the list of the base tables in the given database.This will list all the base tables in the order of the table_schema and table_name.

Query 1 :
--------
Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
TABLE_TYPE as TableType
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE='BASE TABLE'
order by
TABLE_SCHEMA,TABLE_NAME

List of the views:
-----------------
The below query all the list of the views that are there in particular database.

Query 2 :
--------
Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
TABLE_TYPE as TableType
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE='VIEW'

List of all columns in the base tables and views:
----------------------------------------------
The below query will give the list of the columns in the tables that are there in the database.

Query 3 :
--------
Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
COLUMN_NAME,
DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS
order by
TABLE_SCHEMA,TABLE_NAME

List all the constraints:
-------------------------------------------------------

To list all the constraints that are there for all the tables in particular database.

Query 4 :
--------

Select
'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' AS TableName,
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
TABLE_SCHEMA is not null and
TABLE_NAME is not null
order by
TABLE_SCHEMA,TABLE_NAME


Hope this helps

To retrieve all the functions/procedures in the given database -- using Information_schema related view

Most of the SQL Server guys, some times need to retrieve the information about the procedures and functions.

Information about Functions:

----------------------------

The below query gives the no.of functions that are there in specific database and the definition of that specific function.

Query:

-------

Select 

'['+ROUTINE_SCHEMA+'].'+'['+ROUTINE_NAME+']' AS RoutineName,

ROUTINE_TYPE as RoutineType,

ROUTINE_DEFINITION

from 

INFORMATION_SCHEMA.ROUTINES

where 

ROUTINE_TYPE='FUNCTION'

Information about Procedures:

------------------------------

The below query gives the no.of procedures that are there in specific database and the definition of that specific procedure.

Query:

-------

Select 

'['+ROUTINE_SCHEMA+'].'+'['+ROUTINE_NAME+']' AS RoutineName,

ROUTINE_TYPE as RoutineType,

ROUTINE_DEFINITION

from 

INFORMATION_SCHEMA.ROUTINES

where 

ROUTINE_TYPE='PROCEDURE'

GOTO STATEMENT in WHILE LOOP-3

Hello,

In this post, I would like to post the While loop example with GOTO Statement.

Code Snippet :

--WHILE LOOP WITH GOTO STATEMENT
DECLARE @cnt INT
SET @cnt=11
WHILE @cnt>=1
BEGIN
SET @cnt=@cnt-1;
IF @cnt=7
GOTO break_out;
PRINT 'The value of count is :'+CAST(@CNT AS VARCHAR(10));
END
break_out:
GO

Description:

When GOTO statement executed, control will be moved where the GOTO "NAME" given in the code.
In the below example the name given to GOTO is "break_out"

Hope this helps.

Anilkumar

USING CONTINUE STATEMENT IN WHILE-2

Hello,

Below is the example where Continue statement is used in While loop.

Code Snippet:

--USING CONTINUE STATEMENT IN WHILE

DECLARE @CNT INT

SET @CNT=11

WHILE @CNT>=1

BEGIN

--PRINT @CNT;

SET @CNT=@CNT-1;

IF @CNT=7

CONTINUE;

PRINT 'THE VALUE IS :'+CAST(@CNT AS VARCHAR(10));

END

GO

Description :

       

        When the count variable equals to 7 and IF condition will be successful and it executes the keyword continue and skips the print statement for value 7. "Continue" skips all the statements after that continue keyword and before the END. It will execute the next iteration.

Hopefully this helps.

Anilkumar

USING BREAK STATEMENT IN WHILE LOOP

Hello Guys,

All my postings are for beginners.

Below is the example code snippet using break statement in while loop.

Code Snippet:

-- USING BREAK STATEMENT IN WHILE
DECLARE @cnt INT
SET @cnt=10
WHILE @cnt>=1
BEGIN
PRINT @cnt
SET @cnt=@cnt-1
IF(@cnt=5)
BEGIN
PRINT 'Break statement executed when count value is 5'
PRINT 'So values are not printed after value 5'
BREAK;
END
END

GO

Hope this helps.

Anilkumar

Basic While Loop example in T-SQL

Hi All,

Below is the sample while code snippet in T-SQL.

DECLARE @counter INT
SET @counter=10
WHILE @counter>=1
BEGIN
PRINT @counter
SET @counter = @counter-1
END
GO

Thanks,

Anilkumar

Script to Re-name a column name in SQL Server:

We can rename the column name in SQL Server by executing the inbuilt procedure sp_RENAME  by giving 3 parameters

1) Table with ColumnName_old

2) Table with ColumnName_new

3) Object we are changing, in this case it is COLUMN

All the above 3 must be enclosed in single quotes as there are string parameters to the calling procedure.

Syntax:

sp_RENAME '[SchemaName].[TableName].[ColumnName_Old]' , '[ColumnName_New]', 'COLUMN';

Example:

In the above screen I am going to rename the column using system built-in procedure

The column has been successfully re-named.

Hope the above post is helpful.

Cheers,

Anilkumar P