String Split in SQL

 Hi All,

We had a requirement where we had to seperate comma seperated values and unpivot it 

i,e if the column is Valid,Input, Outcome then expected results are 

1. Valid

2. Input

3. Outcome

To acheive this, I used the below query 

Insert Into Claims_Detail

SELECT ClaimsID, splitC.Value

From [pce].[Claims_Dim]

CROSS APPLY STRING_SPLIT([pce].[Claims_Dim].ProductPositionClaimsName,',') splitC

Bingo..Issue fixed.

Query to obtain size of the tables in any DB in SQL

 Use the below query

SELECT 

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows,

    SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM 

    sys.tables t

INNER JOIN      

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN 

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN 

    sys.schemas s ON t.schema_id = s.schema_id

WHERE 

    t.NAME NOT LIKE 'dt%' 

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255 

GROUP BY 

    t.Name, s.Name, p.Rows

ORDER BY 

    TotalSpaceMB DESC, t.Name

How to get column names, dataype information in SQL

 To get column names, datatypes and its maximum length use below code

SELECT C.NAME AS COLUMN_NAME,

TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,

C.MAX_LENGTH

FROM SYS.COLUMNS C

JOIN SYS.TYPES T

ON C.USER_TYPE_ID=T.USER_TYPE_ID

WHERE C.OBJECT_ID=OBJECT_ID('[pce].[Product_Fact]');