Calculate SQL Table Storage


DECLARE @TABLENAME NVARCHAR (100), @COLUMNNAME NVARCHAR( 100),@SQL NVARCHAR(400 ),
@RESULT NVARCHAR( 10)
CREATE TABLE #TMP(
	ID INT,
	TABLENAME NVARCHAR( 100),
	COLUMNAME NVARCHAR( 100),
	SQLQUERY NVARCHAR( 200)
)
CREATE TABLE #TMPCOPY(
	ID INT,
	TABLENAME NVARCHAR( 100),
	COLUMNAME NVARCHAR( 100),
	SQLQUERY NVARCHAR( 200)
)
CREATE TABLE #TMP2(
	ID INT,
	RESULT NVARCHAR( 10)
)
INSERT INTO #TMP
select ROW_NUMBER () OVER (ORDER BY (SELECT 300) ) AS ID,
TABLE_NAME,COLUMN_NAME ,
  'SELECT '+ CONVERT(NVARCHAR (100), ROW_NUMBER() OVER(ORDER BY ( SELECT 300) ))+
  ',MAX(LEN('+ COLUMN_NAME+')) FROM ' + TABLE_NAME
from INFORMATION_SCHEMA .COLUMNS WHERE DATA_TYPE= 'nvarchar'
AND CHARACTER_MAXIMUM_LENGTH= 50 ORDER BY  COLUMN_NAME
INSERT #TMPCOPY
SELECT * FROM #TMP
WHILE EXISTS (SELECT   * FROM #TMP )
BEGIN
SELECT TOP 1  @TABLENAME=TABLENAME ,@COLUMNNAME= COLUMNAME,@SQL =SQLQUERY FROM #TMP
INSERT INTO #TMP2 EXECUTE( @SQL)
DELETE FROM #TMP WHERE TABLENAME =@TABLENAME
END
DROP TABLE #TMP
SELECT * FROM #TMPCOPY LEFT JOIN #TMP2 ON ( #TMP2.ID =#TMPCOPY. ID)
DROP TABLE #TMP2

Leave a Reply

Your email address will not be published.