dhmacher

DECLARE @object_id int=OBJECT_ID('schema_name.table_name');

DECLARE @name nvarchar(max)=(SELECT QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name]) FROM sys.objects WHERE [object_id]=@object_id),
        @cols nvarchar(max)=N'',
        @query nvarchar(max)=N'',
        @pagesize varchar(10)=N'100';


WITH cte AS (
    SELECT c.column_id, c.[name], t.[name] AS [type]

dhmacher

SELECT o.[type],
       ISNULL(NULLIF(t.temporal_type_desc, 'NON_TEMPORAL_TABLE'), o.[type_desc]) AS object_type,
       s.[name]+'.'+o.[name] AS [object_name],
       (CASE WHEN i.is_unique=1 THEN 'UNIQUE ' ELSE '' END)+i.[type_desc] AS index_type,
       (CASE WHEN kc.is_system_named=1 THEN '' ELSE i.[name] END) AS index_name,
       ISNULL('WHERE '+i.filter_definition, '') AS index_filter,
       ISNULL(' ON '+(CASE WHEN ds.is_default=0 THEN ds.[name] END)+ISNULL('('+c.[name]+')', ''), '') AS data_space,
       ISNULL(CAST(NULLIF(NULLIF(i.fill_factor, 0), 100) AS varchar(10))+'%', '') AS fill_factor,
       ISNULL(CAST(p.partition_number AS varchar(10))+'/'+CAST(NULLIF(MAX(p.partition_number) OVER (PARTITION BY p.[object_id], p.index_id), 1) AS varchar(10)), '') AS [partition],
       ISNULL(NULLIF(p.data_compression_desc, 'NONE'), '') AS [compression],