ChatGPT解决这个技术问题 Extra ChatGPT

What is the equivalent of 'describe table' in SQL Server?

I have a SQL Server database and I want to know what columns and types it has. I'd prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this?

Another stackoverflow [question][1] has this answered [1]: stackoverflow.com/questions/11078106/…
I do not understand why Microsoft did not put this option beforehand. This is must have functionality.

V
Vincent Ramdhanie

You can use the sp_columns stored procedure:

exec sp_columns MyTable

Thanks - I was about to post the same question with T-SQL instead of MSSQL.
Just a quick note: don't put the table name in quotes and don't use the TableOwner.TableName syntax.
See here if you're getting no results when you run this query.
use Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME' if you don't want to use a stored procedure
The output from sp_columns is ludicrously unreadable in sqlcmd even using enormous terminal widths. The solutions utilizing select found below are much more appropriate for sqlcmd users.
B
Braiam

There are a few methods to get metadata about a table:

EXEC sp_help tablename

Will return several result sets, describing the table, it's columns and constraints.

The INFORMATION_SCHEMA views will give you the information you want, though unfortunately you have to query the views and join them manually.


For me it worked without the "@" character EXEC sp_help 'table_name'
this is a more correct version to Viranja's answer. The @ is incorrect syntax however.
It looks like @tablename is a variable in the example. The sp works both ways, with either a string or the 'plain' tablename (exec sp_help Employees or exec sp_help 'Employees')
FYI: Schema (and even Database) can go within the ticks: execute sp_help 'db.sch.your_table
Very helpful. Thank you.
S
Salamander2007

Just in case you don't want to use stored proc, here's a simple query version

select * 
  from information_schema.columns 
 where table_name = 'aspnet_Membership'
 order by ordinal_position

In my case this also applies if for some reason those stored procs are not available.
The stored proc is inflexible and really hostile to naive sqlcmd users like me
love this one as it orders by name :D
J
JayRizzo

You can use following: sp_help tablename

Example: sp_help Customer

OR Use Shortcut Keys

Select the desired table and press ALT+F1.

Example: Customer Press ALT+F1.


Note that, if you have the table in a user-defined schema, you should exclude it from the query. All schema containing a table with this name will appear in a column called "TABLE_OWNER" in the result set.
Note that for the shortcut to work the table/view/procedure has to be fully selected. SSMS does not automatically expand the selection (I would have expected that). Ctrl+W can be used to expand the selection and select the whole name.
R
Reinstate Monica -- notmaynard

Use this Query

Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'

Works for me if you say "use MyDatabase" first.
I like this because it works on MySQL too, so I don't have to change my SQL in my code
This answer is not any different to stackoverflow.com/a/319424/695671 which came 5 years prior. I don't find repeated answers useful.
L
Laxmi

In addition to the ways shown in other answers, you can use

SELECT TOP 0 * FROM table_name

This will give you the name of each column with no results in them, and completes almost instantly with minimal overhead.


TOP 1 would give a sample data as well, which might be more illustrative.
A
Abhijeet

Please use the following sql query; this worked for my case.

select * FROM   INFORMATION_SCHEMA.Columns where table_name = 'tablename';

The same answer was given in 2008 and 2014. I don't find repeated answers useful.
A
Alejandro Montilla

Just select table and press Alt+F1,

it will show all the information about table like Column name, datatype, keys etc.


OP asked for a GUI-less method
-1 because the same advice was already given by another user in 2014. Please, respect other people's time.
Z
Zsolt Hidasi

I wrote an sql*plus DESC(RIBE) like select (displays the column comments, too) in t-sql:

USE YourDB
GO

DECLARE @objectName NVARCHAR(128) = 'YourTable';

SELECT
  a.[NAME]
 ,a.[TYPE]
 ,a.[CHARSET]
 ,a.[COLLATION]
 ,a.[NULLABLE]
 ,a.[DEFAULT]
 ,b.[COMMENTS]
-- ,a.[ORDINAL_POSITION]
FROM
  (
    SELECT
      COLUMN_NAME                                     AS [NAME]
     ,CASE DATA_TYPE
        WHEN 'char'       THEN DATA_TYPE  + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
        WHEN 'numeric'    THEN DATA_TYPE  + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
        WHEN 'nvarchar'   THEN DATA_TYPE  + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
        WHEN 'varbinary'  THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
        WHEN 'varchar'    THEN DATA_TYPE   + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
        ELSE DATA_TYPE
      END                                             AS [TYPE]
     ,CHARACTER_SET_NAME                              AS [CHARSET]
     ,COLLATION_NAME                                  AS [COLLATION]
     ,IS_NULLABLE                                     AS [NULLABLE]
     ,COLUMN_DEFAULT                                  AS [DEFAULT]
     ,ORDINAL_POSITION
    FROM   
      INFORMATION_SCHEMA.COLUMNS
    WHERE
      TABLE_NAME = @objectName
  ) a
  FULL JOIN
  (
   SELECT
     CAST(value AS NVARCHAR)                        AS [COMMENTS]
    ,CAST(objname AS NVARCHAR)                      AS [NAME]
   FROM
     ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @objectName, 'column', default)
  ) b
  ON a.NAME COLLATE YourCollation = b.NAME COLLATE YourCollation
ORDER BY
  a.[ORDINAL_POSITION];

The above mentioned select can be used in a system marked stored procedure and it can be called from any database of your instance on a simple way:

USE master;
GO

IF OBJECT_ID('sp_desc', 'P') IS NOT NULL
  DROP PROCEDURE sp_desc
GO

CREATE PROCEDURE sp_desc (
  @tableName  nvarchar(128)
) AS
BEGIN
  DECLARE @dbName       sysname;
  DECLARE @schemaName   sysname;
  DECLARE @objectName   sysname;
  DECLARE @objectID     int;
  DECLARE @tmpTableName varchar(100);
  DECLARE @sqlCmd       nvarchar(4000);

  SELECT @dbName = PARSENAME(@tableName, 3);
  IF @dbName IS NULL SELECT @dbName = DB_NAME();

  SELECT @schemaName = PARSENAME(@tableName, 2);
  IF @schemaName IS NULL SELECT @schemaName = SCHEMA_NAME();

  SELECT @objectName = PARSENAME(@tableName, 1);
  IF @objectName IS NULL
    BEGIN
      PRINT 'Object is missing from your function call!';
      RETURN;
    END;

  SELECT @objectID = OBJECT_ID(@dbName + '.' + @schemaName + '.' + @objectName);
  IF @objectID IS NULL
    BEGIN
      PRINT 'Object [' + @dbName + '].[' + @schemaName + '].[' + @objectName + '] does not exist!';
      RETURN;
    END;

  SELECT @tmpTableName = '#tmp_DESC_' + CAST(@@SPID AS VARCHAR) + REPLACE(REPLACE(REPLACE(REPLACE(CAST(CONVERT(CHAR, GETDATE(), 121) AS VARCHAR), '-', ''), ' ', ''), ':', ''), '.', '');
  --PRINT @tmpTableName;
  SET @sqlCmd = '
    USE ' + @dbName + '
    CREATE TABLE ' + @tmpTableName + ' (
      [NAME]              nvarchar(128) NOT NULL
     ,[TYPE]              varchar(50)
     ,[CHARSET]           varchar(50)
     ,[COLLATION]         varchar(50)
     ,[NULLABLE]          varchar(3)
     ,[DEFAULT]           nvarchar(4000)
     ,[COMMENTS]          nvarchar(3750));

    INSERT INTO ' + @tmpTableName + '
    SELECT
      a.[NAME]
     ,a.[TYPE]
     ,a.[CHARSET]
     ,a.[COLLATION]
     ,a.[NULLABLE]
     ,a.[DEFAULT]
     ,b.[COMMENTS]
    FROM
      (
        SELECT
          COLUMN_NAME                                     AS [NAME]
         ,CASE DATA_TYPE
            WHEN ''char''      THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
            WHEN ''numeric''   THEN DATA_TYPE + ''('' + CAST(NUMERIC_PRECISION AS VARCHAR) + '', '' + CAST(NUMERIC_SCALE AS VARCHAR) + '')''
            WHEN ''nvarchar''  THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
            WHEN ''varbinary'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
            WHEN ''varchar''   THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
            ELSE DATA_TYPE
          END                                             AS [TYPE]
         ,CHARACTER_SET_NAME                              AS [CHARSET]
         ,COLLATION_NAME                                  AS [COLLATION]
         ,IS_NULLABLE                                     AS [NULLABLE]
         ,COLUMN_DEFAULT                                  AS [DEFAULT]
         ,ORDINAL_POSITION
        FROM   
          INFORMATION_SCHEMA.COLUMNS
        WHERE   
          TABLE_NAME = ''' + @objectName + '''
      ) a
      FULL JOIN
      (
         SELECT
           CAST(value AS NVARCHAR)                        AS [COMMENTS]
          ,CAST(objname AS NVARCHAR)                      AS [NAME]
         FROM
           ::fn_listextendedproperty (''MS_Description'', ''user'', ''' + @schemaName + ''', ''table'', ''' + @objectName + ''', ''column'', default)
      ) b
      ON a.NAME COLLATE Hungarian_CI_AS = b.NAME COLLATE Hungarian_CI_AS
    ORDER BY
      a.[ORDINAL_POSITION];

    SELECT * FROM ' + @tmpTableName + ';'

    --PRINT @sqlCmd;

    EXEC sp_executesql @sqlCmd;
    RETURN;
END;
GO

EXEC sys.sp_MS_marksystemobject sp_desc
GO

To execute the procedure type:

EXEC sp_desc 'YourDB.YourSchema.YourTable';

If you want to get a description an object of the current database (and schema) simple type:

EXEC sp_desc 'YourTable';

As sp_desc is a system marked procedure, you can even leave the exec command, too (not recommended anyway):

sp_desc 'YourTable';

V
VHS

The SQL Server equivalent to Oracle's describe command is the stored proc sp_help

The describe command gives you the information about the column names, types, length, etc.

In SQL Server, let's say you want to describe a table 'mytable' in schema 'myschema' in the database 'mydb', you can do following:

USE mydb;
exec sp_help 'myschema.mytable';

u
user3819354

You can use the sp_help 'TableName'


j
jiujiu

try it:

EXEC [ServerName].[DatabaseName].dbo.sp_columns 'TableName'

and you can get some table structure information, such as:

TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME...


can you pls explain the answer a bit?
Thanks for reminding, I have added some explain.
H
Hari_pb

In addition to above questions, if we have table in DB like db_name.dbo.table_name, we may use following steps

Connect with DB USE db_name; Use EXEC sp_help and don't forget to put table name as 'dbo.tablename' if you have dbo as schema. exec sp_help 'dbo.table_name'

This should work!


A
Abd Abughazaleh

I tried this and it's working for me

exec sp_help TABLE_NAME

佚名

The problem with those answers is that you're missing the key info. While this is a bit messy this is a quick version I came up with to make sure it contains the same info the MySQL Describe displays.

Select SC.name AS 'Field', ISC.DATA_TYPE AS 'Type', ISC.CHARACTER_MAXIMUM_LENGTH AS 'Length', SC.IS_NULLABLE AS 'Null', I.is_primary_key AS 'Key', SC.is_identity AS 'Identity'
From sys.columns AS SC 
LEFT JOIN sys.index_columns AS IC
ON IC.object_id = OBJECT_ID('dbo.Expenses') AND 
IC.column_id = SC.column_id
LEFT JOIN sys.indexes AS I 
ON I.object_id = OBJECT_ID('dbo.Expenses') AND 
IC.index_id = I.index_id
LEFT JOIN information_schema.columns ISC
ON ISC.TABLE_NAME = 'Expenses'
AND ISC.COLUMN_NAME = SC.name
WHERE SC.object_id = OBJECT_ID('dbo.Expenses')

is there any reason why is_primary_key would never have a value?
S
Simon Hughes

This is the code I use within the EntityFramework Reverse POCO Generator (available here)

Table SQL:

SELECT  c.TABLE_SCHEMA AS SchemaName,
        c.TABLE_NAME AS TableName,
        t.TABLE_TYPE AS TableType,
        c.ORDINAL_POSITION AS Ordinal,
        c.COLUMN_NAME AS ColumnName,
        CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1
                  ELSE 0
             END AS BIT) AS IsNullable,
        DATA_TYPE AS TypeName,
        ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [MaxLength],
        CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS [Precision],
        ISNULL(COLUMN_DEFAULT, '') AS [Default],
        CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision,
        ISNULL(NUMERIC_SCALE, 0) AS Scale,
        CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity,
        CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
                  WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1
                  WHEN DATA_TYPE = 'TIMESTAMP' THEN 1
                  ELSE 0
             END AS BIT) AS IsStoreGenerated,
        CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS PrimaryKey,
        ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal,
        CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0
                  ELSE 1
             END AS BIT) AS IsForeignKey
FROM    INFORMATION_SCHEMA.COLUMNS c
        LEFT OUTER JOIN (SELECT u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME,
                                u.ORDINAL_POSITION
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY') pk
            ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
               AND c.TABLE_NAME = pk.TABLE_NAME
               AND c.COLUMN_NAME = pk.COLUMN_NAME
        LEFT OUTER JOIN (SELECT DISTINCT
                                u.TABLE_SCHEMA,
                                u.TABLE_NAME,
                                u.COLUMN_NAME
                         FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
                                INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                                    ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
                                       AND u.TABLE_NAME = tc.TABLE_NAME
                                       AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                         WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY') fk
            ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA
               AND c.TABLE_NAME = fk.TABLE_NAME
               AND c.COLUMN_NAME = fk.COLUMN_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLES t
            ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
               AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')

Foreign Key SQL:

SELECT  FK.name AS FK_Table,
        FkCol.name AS FK_Column,
        PK.name AS PK_Table,
        PkCol.name AS PK_Column,
        OBJECT_NAME(f.object_id) AS Constraint_Name,
        SCHEMA_NAME(FK.schema_id) AS fkSchema,
        SCHEMA_NAME(PK.schema_id) AS pkSchema,
        PkCol.name AS primarykey,
        k.constraint_column_id AS ORDINAL_POSITION
FROM    sys.objects AS PK
        INNER JOIN sys.foreign_keys AS f
            INNER JOIN sys.foreign_key_columns AS k
                ON k.constraint_object_id = f.object_id
            INNER JOIN sys.indexes AS i
                ON f.referenced_object_id = i.object_id
                   AND f.key_index_id = i.index_id
            ON PK.object_id = f.referenced_object_id
        INNER JOIN sys.objects AS FK
            ON f.parent_object_id = FK.object_id
        INNER JOIN sys.columns AS PkCol
            ON f.referenced_object_id = PkCol.object_id
               AND k.referenced_column_id = PkCol.column_id
        INNER JOIN sys.columns AS FkCol
            ON f.parent_object_id = FkCol.object_id
               AND k.parent_column_id = FkCol.column_id
ORDER BY FK_Table, FK_Column

Extended Properties:

SELECT  s.name AS [schema],
        t.name AS [table],
        c.name AS [column],
        value AS [property]
FROM    sys.extended_properties AS ep
        INNER JOIN sys.tables AS t
            ON ep.major_id = t.object_id
        INNER JOIN sys.schemas AS s
            ON s.schema_id = t.schema_id
        INNER JOIN sys.columns AS c
            ON ep.major_id = c.object_id
               AND ep.minor_id = c.column_id
WHERE   class = 1
ORDER BY t.name

G
Graham

I like this format:

name     DataType      Collation             Constraints         PK  FK          Comment

id       int                                 NOT NULL IDENTITY   PK              Order Line Id
pid      int                                 NOT NULL                tbl_orders  Order Id
itemCode varchar(10)   Latin1_General_CI_AS  NOT NULL                            Product Code

So I have used this:

DECLARE @tname varchar(100) = 'yourTableName';

SELECT  col.name,

        CASE typ.name
            WHEN 'nvarchar' THEN 'nvarchar('+CAST((col.max_length / 2) as varchar)+')'
            WHEN 'varchar' THEN 'varchar('+CAST(col.max_length as varchar)+')'
            WHEN 'char' THEN 'char('+CAST(col.max_length as varchar)+')'
            WHEN 'nchar' THEN 'nchar('+CAST((col.max_length / 2) as varchar)+')'
            WHEN 'binary' THEN 'binary('+CAST(col.max_length as varchar)+')'
            WHEN 'varbinary' THEN 'varbinary('+CAST(col.max_length as varchar)+')'
            WHEN 'numeric' THEN 'numeric('+CAST(col.precision as varchar)+(CASE WHEN col.scale = 0 THEN '' ELSE ','+CAST(col.scale as varchar) END) +')'
            WHEN 'decimal' THEN 'decimal('+CAST(col.precision as varchar)+(CASE WHEN col.scale = 0 THEN '' ELSE ','+CAST(col.scale as varchar) END) +')'
            ELSE typ.name
            END DataType,

        ISNULL(col.collation_name,'') Collation,

        CASE WHEN col.is_nullable = 0 THEN 'NOT NULL ' ELSE '' END + CASE WHEN col.is_identity = 1 THEN 'IDENTITY' ELSE '' END Constraints,

        ISNULL((SELECT 'PK'
                FROM    sys.key_constraints kc INNER JOIN
                        sys.tables tb ON tb.object_id = kc.parent_object_id INNER JOIN
                        sys.indexes si ON si.name = kc.name INNER JOIN
                        sys.index_columns sic ON sic.index_id = si.index_id AND sic.object_id = si.object_id
                WHERE kc.type = 'PK'
                  AND tb.name = @tname
                  AND sic.column_id = col.column_id),'') PK,

        ISNULL((SELECT (SELECT name FROM sys.tables st WHERE st.object_id = fkc.referenced_object_id)
                FROM    sys.foreign_key_columns fkc INNER JOIN
                        sys.columns c ON c.column_id = fkc.parent_column_id AND fkc.parent_object_id = c.object_id INNER JOIN
                        sys.tables t ON t.object_id = c.object_id
                WHERE t.name = tab.name
                  AND c.name = col.name),'') FK,

        ISNULL((SELECT value
                FROM sys.extended_properties
                WHERE major_id = tab.object_id
                  AND minor_id = col.column_id),'') Comment

FROM sys.columns col INNER JOIN
     sys.tables tab ON tab.object_id = col.object_id INNER JOIN
     sys.types typ ON typ.system_type_id = col.system_type_id
WHERE tab.name = @tname
  AND typ.name != 'sysname'
ORDER BY col.column_id;

m
maneesh

First connect to your DB,

use DB_name

Then

exec sp_help 'Production.Et_Issue'

here 'production' is the schema name. If you dont have a schema, you may simply write sp_help table_name

https://i.stack.imgur.com/Hp57F.png


n
nikeee

use

SELECT COL_LENGTH('tablename', 'colname')

None of other solution worked for me.


this requires knowing what the other columns are. Also there is a a typo
Fixed the quote.
a
abhishek khanna
SELECT C.COLUMN_NAME, C.IS_NULLABLE, C.DATA_TYPE, TC.CONSTRAINT_TYPE, C.COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS AS C
    FULL JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CC ON C.COLUMN_NAME = CC.COLUMN_NAME 
    FULL JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE C.TABLE_NAME = '<Table Name>';

Sample Output


Welcome to SO! When you reply to a question, try to explain your answer a little bit. In your case, there are 16 more replies so you should expose the Pros and Cons of your answer
D
Dmitriy Grishin - dogrishin

If you are using FirstResponderKit from Brent Ozar team, you can run this query also:

exec sp_blitzindex @tablename='MyTable'

It will return all information about table:

indexes with their usage statistics(reads, writes, locks, etc), space used and other

missing indexes

columns

foreign keys

statistics contents

Of course it's not a system and not so universal stp like sp_help or sp_columns, but it returns all possible information about your table and I think it's worth creating it at your environment and mentioning it here.


m
mannedear

Just double click on the table name and press Alt+F1


A
Andrzej Bobak
CREATE PROCEDURE [dbo].[describe] 
( 
@SearchStr nvarchar(max) 
) 
AS 
BEGIN 
SELECT  
    CONCAT([COLUMN_NAME],' ',[DATA_TYPE],' ',[CHARACTER_MAXIMUM_LENGTH],' ', 
    (SELECT CASE [IS_NULLABLE] WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END),
    (SELECT CASE WHEN [COLUMN_DEFAULT] IS NULL THEN '' ELSE CONCAT(' DEFAULT ',[COLUMN_DEFAULT]) END)
    ) AS DESCRIPTION
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE @SearchStr
END 

D
David Buck

The query below will provide similar output as the info() function in python, Pandas library.

USE [Database_Name]

IF OBJECT_ID('tempdo.dob.#primary_key', 'U') IS NOT NULL DROP TABLE #primary_key

SELECT 
CONS_T.TABLE_CATALOG,
CONS_T.TABLE_SCHEMA,
CONS_T.TABLE_NAME,
CONS_C.COLUMN_NAME,
CONS_T.CONSTRAINT_TYPE,
CONS_T.CONSTRAINT_NAME
INTO  #primary_key
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CONS_T 
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CONS_C ON CONS_C.CONSTRAINT_NAME= CONS_T.CONSTRAINT_NAME


SELECT
SMA.name AS [Schema Name],
ST.name AS [Table Name],
SC.column_id AS [Column Order],
SC.name AS [Column Name],
PKT.CONSTRAINT_TYPE, 
PKT.CONSTRAINT_NAME, 
SC.system_type_id,
STP.name AS [Data Type],
SC.max_length,
SC.precision, 
SC.scale, 
SC.is_nullable, 
SC.is_masked
FROM sys.tables  AS ST
JOIN sys.schemas AS SMA ON SMA.schema_id = ST.schema_id
JOIN sys.columns AS SC ON SC.object_id = ST.object_id 
JOIN sys.types AS STP ON STP.system_type_id = SC.system_type_id
LEFT JOIN #primary_key AS PKT ON PKT.TABLE_SCHEMA = SMA.name
                                 AND PKT.TABLE_NAME = ST.name
                                 AND PKT.COLUMN_NAME = SC.name
ORDER BY ST.name ASC, SMA.name ASC