MSBI

Hi All, Here is function to make first letter capital in a word, which is also known as "Pascal Naming Convention" CREATE FUNCTION [dbo] . [InitCap] ( @InputString VARCHAR ( 4000 ) ) RETURNS VARCHAR ( 4000 ) AS BEGIN DECLARE @Index INT DECLARE @Char CHAR ( 1 ) DECLARE @PrevChar CHAR ( 1 ) DECLARE @OutputString VARCHAR ( 255 ) SET @OutputString = LOWER ( @InputString ) SET @Index = 1 WHILE @Index <= LEN ( @InputString ) BEGIN SET @Char = SUBSTRING ( @InputString , @Index , 1 ) SET @PrevChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING ( @InputString , @Index - 1 , 1 ) END IF @PrevChar IN ( ' ' , ';' , ':' , '!' , '?' , ',' , '.' , '_' , '-' , '/' , '&' , '''' , '(' ) BEGIN IF @PrevChar != &

Hi All, We when planned to delete some unused tables and also to know how much some of tables consuming the space, we needed to have a quick on table size. Then team approached me to write a quick query to know the table name and its size. The query to calculate size of table in given database is as follows: SELECT t.NAME AS TableName ,s.NAME AS SchemaName ,p.rows AS RowCounts ,SUM(a.total_pages) * 8 AS TotalSpaceKB ,SUM(a.used_pages) * 8 AS UsedSpaceKB ,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 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 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 t.NAME