SQL Server equivalent of Oracle’s desc tablename command

Database Management Systems
SQL Server
Thank you all for the response to my previous query. What is the T-SQL equivalent of Oracle's desc command? I mean, what is the command in T-SQL to see the structure of a particular table? And what do we do in T-SQL if we want to see list of all the tables in a particular database?

Answer Wiki

Thanks. We'll let you know when a new response is added.

sp_columns ‘tablename’

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • BenjiT
    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
    0 pointsBadges:
  • Sumeshs
    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
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: