GET all columns of a table–TIP #4

 

To Get all columns information of a table we can use following command

GO

SELECT st.name, sc.name,sc.is_identity AS IdentityColumn,
sc.is_nullable AS NullableColumn,
sc.max_length as colmaxlength,
sty.name as columnType
FROM sys.columns sc
INNER JOIN sys.tables st ON st.object_id  = sc.object_id
INNER JOIN systypes sty on sty.xtype = sc.system_type_id
ORDER By st.name,sc.name
GO

sp_Columns_Detail_Rajat_indiandotnet

Or we can use following statement

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
FROM [INFORMATION_SCHEMA].[COLUMNS]

sp_column_Details_2

Enjoy Smile !!!

sp_help a way to know detail tip #2

To determine table detail like column name & data types for each individual table ,Index, primary key, foreign keys etc,  sp_help is one of the best way.

Syntax of sp_help is very simple as shown below

sp_help tableName

GO

sp_help_table_name_Rajat_Jaiswal_Indiandotnet

Instead of writing  sp_help tablename you can get same information by highlighting table and press ALT+ F1

Note: if you just right sp_help and run it then all the objects of database will be listed down as a result.

sp_helpDB

Enjoy Smile !!!

SP_SPACEUSED–Determine size (Space) – SQL Server TIP #1

 

To determine size of table in a database or size of database, we have simple syntax in SQL Server which is SP_SPACEUSED.

Syntax is simple enough as shown below

1) If we want to determine size of the database then we have to write below statement and execute

Use DatabaseName

GO

SP_SPACEUSED

GO

sp_spaceused_DB

2) Now to determine size of particular table  we need to write following syntax

GO

SP_SPACEUSED TABLENAME

GO

sp_spaceused_tableName_Rajat_Jaiswal

3) Now to determine size of each table in a database we need to write following syntax

GO

SP_MSFOREACHTABLE ‘SP_SPACEUSED [1]’

GO

sp_MSForEachTable_sp_spaceUsed_Indiandotnet

4) When we are running sp_spaceused it may possible the size is not updated so to make sure size is updated we need to write following command

GO

SP_SPACEUSED @updateUsage =N’True’

GO

sp_spaceused_Update_Usage

In the output of each statements we are seeing reserved, data,Index_Size & unused columns.

so

1) reserved column shows total space reserved by object.

2) data column shows total space used by the data.

3) index_Size shows total space used by index in object.

4)unused shows total space reserved by object but not yet used.

For more information you can visit following link

http://msdn.microsoft.com/en-us/library/ms188776.aspx

Enjoy.