How to list the column names into one row for each table in a SQL Server database

Introduction

This script is to list the column names in one row for each table in a SQL Server database.

Scenarios

Sometimes, people want to list the column names into one row for each table in a SQL Server database. This script will provide some help to get the information.

Script

You can use this script in this way:

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a “New Query”, copy the code fromListAllColumnsInOneRow.sql, paste it and run the script.

After the script finishes running, we’ll get the following figure:

 

 

Here are some code snippets for you reference:

SQL
Edit|Remove
mysql
-- Store all the information of database name,schema name, table name and column name in a table 
IF OBJECT_ID('testtable','u') IS NOT NULL 
DROP TABLE testtable 
GO 
SELECT DB_NAME() AS DatabaseName,SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, c.name AS ColumnName 
INTO testtable 
FROM   
sys.tables t  
JOIN sys.columns c  
ON t.object_id= c.object_id  
WHERE t.type = 'U' 
AND t.is_ms_shipped = 0 
AND t.name <> 'sysdiagrams' 
 

-- Store all the information of database name,schema name, table name and column name in a table  
IF OBJECT_ID('testtable','u') IS NOT NULL  
DROP TABLE testtable  
GO  
SELECT DB_NAME() AS DatabaseName,SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, c.name AS ColumnName  
INTO testtable  
FROM    
sys.tables t   
JOIN sys.columns c   
ON t.object_id= c.object_id   
WHERE t.type = 'U'  
AND t.is_ms_shipped = 0  
AND t.name <> 'sysdiagrams'  
 



 

 

Prerequisites

SQL Server 2005 or higher version