I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much.
But now i also need to get, in the same table, against a column - TRUE
if that column is a primary key.
How do i do this?
My expected output is:
Column name | Data type | Length | isnull | Pk
You can get the MySQL table columns data type with the help of “information_schema. columns”. SELECT DATA_TYPE from INFORMATION_SCHEMA. COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'.
using 'information_schema. columns' we can view info for all tables columns including viewing table name and schema.
To get the column name of a table we use sp_help with the name of the object or table name. sp_columns returns all the column names of the object. The following query will return the table's column names: sp_columns @table_name = 'News'
To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.
SELECT
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('YourTableName')
Just replace YourTableName
with your actual table name - works for SQL Server 2005 and up.
In case you are using schemas, replace YourTableName
by YourSchemaName.YourTableName
where YourSchemaName
is the actual schema name and YourTableName
is the actual table name.
The stored procedure sp_columns returns detailed table information.
exec sp_columns MyTable
You could use the query:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, DATETIME_PRECISION,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName'
to get all the metadata you require except for the Pk information.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With