For any object in SQL Server you can use the system stored procedure sp_help.

If you simply type SP_HELP in a query window you will get a list of all db objects including views, table, stored procedures, etc.

If you type sp_help SomeTableName it will provide a lot of information regarding that table. Table columns, datatype, nullability, etc. It also shows constraints on your table, etc. It tells you if you have an Identity column defined and the parameters for the Identity column. There are number of other things it provides…great tool.

sp_index SomeTableName will provide detail index list and the index properties.

If you are doing database work much in SQL server it will save you a lot of time to read through Books Online or TSQL Help on the stored procedures starting with SP_ and XP_. There are lot that you can skip for scheduling and replication if you are not using those featuers. But, the others will provide a lot of information.

Cheers

Use this script, create it it in the database you need and grant exec to public.

ALTER procedure Descr(@pi_table varchar(30), @pi_schema Varchar(30)= ‘dbo’)
AS
BEGIn
DECLARE
@l_table Char(30),
@l_col Char(30),
@l_typ VarChar(30),
@l_colH Char(30),
@l_typH Char(20),
@l_prec int,
@l_scale int,
@l_len int,
@l_null CHAR(10),
@l_nullH CHAR(10),
@l_msg Varchar(1000),
@l_first int,
@c1 CURSOR
–Start

IF (@pi_schema is NULL)
set @pi_schema = ‘dbo’

set @l_colH = ‘Column Name’
set @l_nullH = ‘NULL?’
set @l_typH = ‘Data Type’

set @c1 = Cursor
For
select obj.name,
col.name,
upper(typ.name),
ISNULL(col.xprec, 0),
ISNULL(col.scale,0),
col.length,
case col.isnullable when 1 then ‘NULL’ else ‘NOT NULL’ end
FROM sysobjects obj,
syscolumns col,
systypes typ
where obj.id = col.id
AND obj.xtype in (‘U’, ‘V’)
AND col.xusertype = typ.xusertype
and obj.name = @pi_Table
and user_name(obj.uid) = @pi_schema

OPen @C1
fetch next from @c1
into @l_table,@l_col,@l_typ,@l_prec,@l_scale,@l_len,@l_null
IF (@@fetch_status 0 )
BEGIN
set @l_msg = ‘Object Not found in the Schema ‘+ @pi_schema + ‘ of The Database ‘ + db_NAme()
Print @l_msg
END
set @l_first = 1
While (@@fetch_status = 0)
BEGIN
IF @l_first = 1
BEGIN
set @l_msg = ‘TSQL > ‘ + ‘DESC ‘ + @l_table
print @l_msg
–set @l_msg = ‘——————– ————- ————-’
–print @l_msg
set @l_msg = @l_colH + @l_nullH + @l_typH
print @l_msg
set @l_msg = ‘——————– ——– ———–’
print @l_msg
END
–print @l_typ
If (@l_typ = ‘CHAR’) or (@l_typ = ‘VARCHAR’)
set @l_typ = @l_typ + ‘(‘ + cast(@l_len as varchar) + ‘)’
If (@l_typ = ‘NUMERIC’) or (@l_typ = ‘DECIMAL’)
set @l_typ = @l_typ + ‘(‘ + cast(@l_prec as varchar)+ ‘,’+ cast(@l_scale as varchar)+ ‘)’

set @l_msg = @l_col + @l_null + @l_typ
print @l_msg
set @l_first = 2
fetch next from @c1
into @l_table,@l_col,@l_typ,@l_prec,@l_scale,@l_len,@l_null
END
close @c1
deallocate @c1
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The syntax is “descr ‘tablename’”

Have fun,
Sumesh