How should I get a list of table names and field names from SQL server database?

Question

For example, the database name is dbxyz

How should I pull all the table names and field names from the database, then displaying them in DataGridView?

Is it possible to do so? If so, how should I do that? At a minimum, I want to get a list of table names.

Thanks.

Answer 1

The following is done with .NET5, C#9

Class for data operations

namespace SqlOperations.Classes  
{  
    public class DataOperations  
    {  
        private static readonly string ConnectionString = "TODO";  
          
  
        public static DataTable DatabaseTableDetails()  
        {  
            using var cn = new SqlConnection(ConnectionString);  
            using var cmd = new SqlCommand { Connection = cn, CommandText = DatabaseDetailsSelectStatement };  
              
            cn.Open();  
            var dt = new DataTable();  
              
            dt.Load(cmd.ExecuteReader());  
  
            return dt;  
        }  
  
        private static string DatabaseDetailsSelectStatement =>  
            @"  
            SELECT	syso.name [Table],  
		            sysc.name [Field],   
		            sysc.colorder [FieldOrder],   
		            syst.name [DataType],   
		            sysc.[length] [Length],   
		            sysc.prec [Precision],   
            CASE WHEN sysc.scale IS null THEN '-' ELSE sysc.scale END [Scale],   
            CASE WHEN sysc.isnullable = 1 THEN 'True' ELSE 'False' END [AllowNulls],   
            CASE WHEN sysc.[status] = 128 THEN 'True' ELSE 'False' END [Identity],   
            CASE WHEN sysc.colstat = 1 THEN 'True' ELSE 'False' END [PrimaryKey],  
            CASE WHEN fkc.parent_object_id is NULL THEN 'False' ELSE 'True' END [ForeignKey?],   
            CASE WHEN fkc.parent_object_id is null THEN '-' ELSE obj.name  END [RelatedTable],  
            CASE WHEN ep.value is NULL THEN '-' ELSE CAST(ep.value as NVARCHAR(500)) END [Description]  
            FROM [sys].[sysobjects] AS syso  
            JOIN [sys].[syscolumns] AS sysc on syso.id = sysc.id  
            LEFT JOIN [sys].[systypes] AS syst ON sysc.xtype = syst.xtype and syst.name != 'sysname'  
            LEFT JOIN [sys].[foreign_key_columns] AS fkc on syso.id = fkc.parent_object_id and sysc.colid = fkc.parent_column_id      
            LEFT JOIN [sys].[objects] AS obj ON fkc.referenced_object_id = obj.[object_id]  
            LEFT JOIN [sys].[extended_properties] AS ep ON syso.id = ep.major_id and sysc.colid = ep.minor_id and ep.name = 'MS_Description'  
            WHERE syso.type = 'U' AND  syso.name != 'sysdiagrams'  
            ORDER BY [Table], FieldOrder, Field;  
";  
  
  
    }  
}  

Helper class

namespace DataGridViewHelpers  
{  
    public static class DataGridViewExtensions  
    {  
        public static void ExpandColumns([NotNull] this DataGridView sender)  
        {  
            foreach (DataGridViewColumn col in sender.Columns)  
            {  
                // ensure we are not attempting to do this on a Entity  
                if (col.ValueType.Name != "ICollection`1")  
                {  
                    col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;  
                }  
            }  
        }  
  
    }  
}  

Form code

public partial class ShowDatabaseDetailsForm : Form  
{  
    public ShowDatabaseDetailsForm()  
    {  
        InitializeComponent();  
          
        Shown += OnShown;  
    }  
  
    private void OnShown(object? sender, EventArgs e)  
    {  
        dataGridView1.DataSource = DataOperations.DatabaseTableDetails();  
        dataGridView1.ExpandColumns();  
    }  
}  

To get one table

--- this would be a parameter for the command object  
DECLARE @TableName AS nvarchar(20) = 'BusinessEntityPhone'  
  
SELECT	syso.name [Table],  
		sysc.name [Field],   
		sysc.colorder [FieldOrder],   
		syst.name [DataType],   
		sysc.[length] [Length],   
		sysc.prec [Precision],   
CASE WHEN sysc.scale IS null THEN '-' ELSE sysc.scale END [Scale],   
CASE WHEN sysc.isnullable = 1 THEN 'True' ELSE 'False' END [AllowNulls],   
CASE WHEN sysc.[status] = 128 THEN 'True' ELSE 'False' END [Identity],   
CASE WHEN sysc.colstat = 1 THEN 'True' ELSE 'False' END [PrimaryKey],  
CASE WHEN fkc.parent_object_id is NULL THEN 'False' ELSE 'True' END [ForeignKey?],   
CASE WHEN fkc.parent_object_id is null THEN '-' ELSE obj.name  END [RelatedTable],  
CASE WHEN ep.value is NULL THEN '-' ELSE CAST(ep.value as NVARCHAR(500)) END [Description]  
FROM [sys].[sysobjects] AS syso  
JOIN [sys].[syscolumns] AS sysc on syso.id = sysc.id  
LEFT JOIN [sys].[systypes] AS syst ON sysc.xtype = syst.xtype and syst.name != 'sysname'  
LEFT JOIN [sys].[foreign_key_columns] AS fkc on syso.id = fkc.parent_object_id and sysc.colid = fkc.parent_column_id      
LEFT JOIN [sys].[objects] AS obj ON fkc.referenced_object_id = obj.[object_id]  
LEFT JOIN [sys].[extended_properties] AS ep ON syso.id = ep.major_id and sysc.colid = ep.minor_id and ep.name = 'MS_Description'  
WHERE syso.type = 'U' AND  syso.name != 'sysdiagrams' AND syso.name = @TableName  
ORDER BY [Table], FieldOrder, Field;  

To get table names suitable to place in a combo box for selections of the above query.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  

Answer 2

You can query the catalog or INFORMATION_SCHEMA views:

SELECT
     s.name AS SchemaName
    ,t.name AS TableName
    ,c.name AS ColumnName
FROM sys.schemas AS s
JOIN sys.tables AS t ON t.schema_id = s.schema_id
JOIN sys.columns AS c ON c.object_id = t.object_id
ORDER BY
     SchemaName
    ,TableName
    ,ColumnName;

SELECT
     TABLE_SCHEMA AS SchemaName
    ,TABLE_NAME AS TableName
    ,COLUMN_NAME AS ColumnName
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY
     SchemaName
    ,TableName
    ,ColumnName;