Sql
query or script to get or list out all the columns of a given or specified user
defined table in sql server
Sql query
to get the name of all columns of table
SELECT c.name AS ColumnName
FROM sys.tables t INNER JOIN sys.columns c
ON t.object_id
= c.object_id AND t.name = 'TableName'
Note: In above query
change the table name.
Here sys.tables
and sys.columns are system view. sys.tables keeps only user defined tables.
sys.columns
is a system view which keeps the information about all the columns of table.
object_id:
In sql server all the objects like tables, views, stored procedures etc. has
unique id which is known as object id.
Sql query to get
the all columns of a table of given data type. For example:
Sql query to get
the all columns of table which data types are varchar
SELECT c.name AS TotalColumn
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.name = 'Student' AND ty.name = 'varchar'
Another
script:
SELECT c.name AS TotalColumn
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'Student' AND TYPE_NAME(c.user_type_id) = 'varchar'
Sql query to get
the all columns of table which data types are int
SELECT c.name AS TotalColumn
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.name = 'Student' AND ty.name = 'int'
Another
script:
SELECT c.name AS TotalColumn
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'Student' AND TYPE_NAME(c.user_type_id) = 'int'
No comments:
Post a Comment