Which SQL Command How to Use to See the Structure of a Table on SQL Server

How can I show the table structure in SQL Server query?

For SQL Server, if using a newer version, you can use

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

There are different ways to get the schema. Using ADO.NET, you can use the schema methods. Use the DbConnection's GetSchema method or the DataReader'sGetSchemaTable method.

Provided that you have a reader for the for the query, you can do something like this:

using(DbCommand cmd = ...)
using(var reader = cmd.ExecuteReader())
{
    var schema = reader.GetSchemaTable();
    foreach(DataRow row in schema.Rows)
    {
        Debug.WriteLine(row["ColumnName"] + " - " + row["DataTypeName"])
    }
}

See this article for further details.

Describe table structure

sp_help tablename in sql server -- sp_help [ [ @objname = ] 'name' ]

desc tablename in oracle -- DESCRIBE { table-Name | view-Name }

Which SQL command can I use to see the structure of a table on SQL Server?

sp_help '<TableName>'

will give you the structure of a table

Also you can use the information_Schema to get a detailed information,

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' 

Check this out http://msdn.microsoft.com/en-us/library/ms186778.aspx

get basic SQL Server table structure information

Instead of using count(*) you can SELECT * and you will return all of the details that you want including data_type:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Address'

MSDN Docs on INFORMATION_SCHEMA.COLUMNS

How can I retrieve the table structure from a sql database?

You can use information schema.

SELECT 
  COLUMN_NAME,
  DATA_TYPE, 
  IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableName'

How to view table structure sql query?

You can use SHOW CREATE TABLE:

SHOW CREATE TABLE oldtable

T-SQL query to show table definition?

There is no easy way to return the DDL. However you can get most of the details from Information Schema Views and System Views.

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_NAME = 'Customers'

SELECT name, type_desc, is_unique, is_primary_key
FROM sys.indexes
WHERE [object_id] = OBJECT_ID('dbo.Customers')

How to monitor table structure changes in SQL Server?

You could use a ddl trigger on the database

create table dbo.logtablechanges
(
    tableobject_id int,
    tablename sysname,
    columnname sysname,--log a single column alteration
    theaction varchar(50),
    actionbylogin sysname constraint df_logtablechanges_actionbylogin default(original_login()),
    thestatement nvarchar(max),
    thedate datetime constraint df_logtablechanges_thedate default(getdate())
);
go

create table dbo.guineapigtbl(id int identity);
insert into dbo.guineapigtbl default values;
select * from guineapigtbl;
go

create trigger trig_db_alter_table on database
for ALTER_TABLE 
as
begin

    declare @d xml = EVENTDATA();

    declare @tblschemaname sysname, 
            @tblname sysname, 
            @action varchar(20),
            @colname sysname,
            @sqlcommand nvarchar(max);

    --for multiple columns      
    select 
        @tblschemaname = @d.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
        @tblname = @d.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
        @action = @d.value('local-name((/EVENT_INSTANCE/AlterTableActionList/*)[1])', 'varchar(20)'),
        --change this for multiple columns (depends on the logging)
        @colname = @d.value('(/EVENT_INSTANCE/AlterTableActionList/*[1]/Columns/Name)[1]', 'sysname'),
        @sqlcommand = @d.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');

    if object_id(quotename(@tblschemaname) + '.' + quotename(@tblname)) = object_id('dbo.guineapigtbl')
      and @colname is not null
    begin
        insert into dbo.logtablechanges
        (
            tableobject_id, 
            tablename, columnname,  theaction, thestatement
        )
        values
        (
            object_id(quotename(@tblschemaname) + '.' + quotename(@tblname)),
            @tblname, @colname, @action, @sqlcommand
        )

    end

end
go

--testing
alter table dbo.guineapigtbl add col1 int;
alter table dbo.guineapigtbl add col2 varchar(10);
alter table dbo.guineapigtbl alter column col1 bigint;
alter table dbo.guineapigtbl drop column col1;

--multiple
alter table dbo.guineapigtbl add col3 int, col4 int;

--check log
select *
from logtablechanges

--cleanup
/*
drop table dbo.logtablechanges
drop table dbo.guineapigtbl
drop trigger trig_db_alter_table on database

go
*/

---

Related Topics