Table Structure

[maverick786us]

I am using this query to find out the table structure and it works absolutely well


SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'tbl_myTable'


The only problem with this query is, it is unable to figure out whether a certain field is primary key, foreign key, Identity or its type etc. So what all alterations do I have to make in order to get that kind of output??

I am getting this output


Field_ID smallint NULL
Field_Name varchar 50
Description varchar 1024
Alias_Name varchar 50


But I want this output


Field_ID smallint Identity(1,1) NULL
Field_Name varchar 50
Description varchar 1024
Alias_Name varchar 50



I meand Field_ID is an identity (1,1) which is not getting displayed

Thanks in Advance

[hspc]

you can use sp_help stored procedure
it will give you all the details you need. Then you can reformat them as you want.
sp_help TableName

[maverick786us]

But this is not an SQL Query. Can i run it in a stored procedure or from front-end??

[hspc]

Sure,
As you can notice, when you run this SP from query analyzer, it returns multiple result sets.
So assuming you use .net framework as to develop the client application, if you open this query using a data adapter and a dataset, the dataset will be filled with multiple tables, one for each result set.
If you open it using command.ExecuteReader(), the returned datareader will have multiple result sets that you can read one by one using NextResultSet() method.

[maverick786us]

Thanks a lot

[andreasblixt]

If you're using SQL Server 2005, it has much better views in the 'sys' schema than in the old 'INFORMATION_SCHEMA' schema. Here's how you get information about columns:
SELECT * FROM sys.columns;
You'll notice there's an is_identity column which is 0 for false and 1 for true.

If you're only looking for identity columns, here's a better way to get them:
SELECT * FROM sys.identity_columns;

If you want to get columns for a specific table, change your query like this:
SELECT * FROM sys.identity_columns ic
INNER JOIN sys.objects o ON o.object_id = ic.object_id
WHERE o.name = 'YourTableName';

So to get the fields you want , you would do this:
SELECT c.name, t.name, c.max_length, c.is_identity
FROM sys.columns c
INNER JOIN sys.types t
ON t.system_type_id = c.system_type_id
INNER JOIN sys.objects o
ON o.object_id = c.object_id
WHERE o.name = 'Errands';

And for more detailed information about the identity column, you'd do this:
SELECT c.name, t.name, c.max_length, ic.seed_value,
ic.increment_value, ic.last_value
FROM sys.columns c
LEFT JOIN sys.identity_columns ic
ON c.is_identity = 1 AND ic.object_id = c.object_id
INNER JOIN sys.types t
ON t.system_type_id = c.system_type_id
INNER JOIN sys.objects o
ON o.object_id = c.object_id
WHERE o.name = 'Errands';