CODEWITHSUNDEEP
c#sql-server
user1195503
user1195503

Reputation: 21

Get All the information of table in SQL Server

I want to get all the information about a table. Like tableName, columnName , InPrimaryKey , Is UniqueKey , Is Identity , Datatype, Maxlength, Is ForiegnKey

by using inbuilt api of SQL SERVER like information_schema.columns

Upvotes: 0

Views: 1420

Answers (4)

user1054326
user1054326

Reputation: 318

If you want a working project, my stored procedure generator / class object creator reads the database schema for all tables and views in a database.

The code is available at http://radstudio.codeplex.com

The file called DataClassBuilder.Net.dll contains a method called LoadDatabaseSchema() and LoadDataFieldsSchema() should give you all the information you need about the data.

Upvotes: 0

usr
usr

Reputation: 171178

You can do a

select * from table where 0 = 1

into a data-table. The columns will be copied and are ready to be inspected.

Upvotes: 0

anon
anon

Reputation:

You could start with:

EXEC sp_help 'dbo.tablename';

Then you could look at the columns directly:

SELECT 
  c.name,
  [type] = t.name,
  c.max_length,
  c.[precision],
  c.[scale],
  c.is_nullable,
  c.is_identity 
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.user_type_id = t.user_type_id
WHERE c.[object_id] = OBJECT_ID('dbo.tablename');

Indexes and participation in PK & unique constraints, foreign keys etc. are a little more complex, since multiple columns can participate in any of these entities. Here are indexes and PK/UQ constraints:

SELECT 
    [index] = i.name, 
    i.type_desc, 
    i.is_unique, 
    i.is_primary_key, 
    i.is_unique_constraint,
    c.name,
    ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.[object_id] = ic.[object_id]
INNER JOIN sys.columns AS c
ON ic.[object_id] = c.[object_id]
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
WHERE i.[object_id] = OBJECT_ID('dbo.tablename');

And then you could move on to foreign keys by looking at sys.foreign_keys and sys.foreign_key_columns. This is even more convoluted than above - are you looking for all the foreign keys of the current table that point at other tables, all the foreign keys in other tables that point at this one, or both?

Upvotes: 2

usr
usr

Reputation: 171178

Use SQL Server Management Objects (SMO). This is a convenient and fully managed API to get and manipulate the schema of a SQL Server database.

Upvotes: 1

Related Questions