@echo off
setlocal EnableDelayedExpansion

rem
rem    Place database tables into HTML table format
rem    

rem ------------------ Globals ---------------
if (%4)==() goto usage

set SERVER=%1
set TABLE=%2
set USER=%3
set PW=%4
set output=Dictionary.html


set O=(
set C=)
set WHITE=#FFFFFF
set GRAY=#C0C0C0

goto start


rem -------------- USAGE HELP ----------------
:Usage
echo.
echo Usage: DataDictionary ^<server^> ^<tables^> ^<user^> ^<password^>
echo.
echo        Example: 
echo            DataDictionary 192.168.1.1 AdventureWorks dbadmin 11AdvAdmin08
echo.
echo This script gets an HTML version of all the tables in the database
echo See %output% when done
echo.
echo.
goto :EOF



rem ------------------------------------------------------- MAIN ------------------------------------------------------
:start
echo Start time:
time /T


rem --------- Write Header HTML ------------
echo ^<HTML^>^<body^>^<h1^>Data Dictionary and Table Notes^</h1^> > %output%

echo ^<pre^>This data dictionary was originally created with DataDictionary.CMD running against %TABLE% >> %output%
for /F "tokens=*" %%t in ('date /T') do echo Initially created on on %%t >> %output%
echo. >> %output%
echo Each table in the %TABLE% database is named, >> %output%
echo   then statistics on the table are provided, followed by a grid that explains each field in the table. >> %output%
echo   For table columns that have a limited set of distinct values, those distinct values are listed. Each >> %output%
echo   distinct value is preceded with the percentage of times that distinct value appears in the table. >> %output%
echo. >> %output%

echo ^</pre^> >> %output%


rem -------- Get List of All Tables -------
set NO_OUTPUT=1
call :select "Name from Sys.Tables"
type out.txt | sort > tlist.txt


rem Batch "for" loops don't like open or close parens, so pre-create SQL files

echo select cast(column_name as varchar(20)), > cols.sql
echo cast(Data_Type as varchar(20)),          >> cols.sql
echo cast(Is_Nullable as varchar(5))          >> cols.sql


rem --------------------- MAIN LOOP: FOR EACH TABLE -----------------------
for /f %%b in (tlist.txt) do (


  rem -------------- HEADER TABLE NAME ---------------
  echo ^<h1^> %%b ^</h1^>  >> %output%


  rem ------- TABLE STATS AND DATA -------
  echo ^<pre^> >> %output%
  set NO_OUTPUT=1
  echo sp_spaceused '%%b' > in.txt
  call :select in.txt
  set rows=1
  type out.txt | find /V "rows affected"  | find /V "---" | find /V "index_size" > in.txt
  for /F "tokens=2,3,5,7,9 delims= " %%c in (in.txt) do (
     set rows=%%c
     echo Rows in table: %%c >> %output%
     echo Data size:     %%e KB >> %output%
     echo Index size:    %%f KB >> %output%
     echo Unused Space:  %%f KB >> %output%
  )

  rem ---- This search is just for me, commenting it out for the public
  rem 
  rem for /F "tokens=*" %%z in ('findstr /I /s /m /C:"INSERT INTO [%%b]" ..\..\..\*.sql') do (
  rem    echo Table Updates: %%~nz.sql >> %output%
  rem )
  rem
  echo ^</pre^> >> %output%

  echo %%b -- rows: !rows! 

  rem -------- LIST ALL COLUMNS -----------
  echo ^<table border='1'^>       >> %output%
  echo ^<tr^>          >> %output%
  echo   ^<td^>^<b^>Column Name^</b^>^</td^> >> %output%
  echo   ^<td^>^<b^>DataType^</b^>^</td^> >> %output%
  echo   ^<td^>^<b^>Nullable^</b^>^</td^> >> %output%
  echo   ^<td^>^<b^>Developer Notes^</b^>^</td^> >> %output%
  echo   ^<td^>^<b^>Distinct Range of Values^</b^>^</td^> >> %output%
  echo ^</tr^> >> %output%

  set NO_OUTPUT=1
  type cols.sql > in.txt
  echo  from information_schema.columns where table_name='%%b'  >> in.txt
  call :select in.txt 
  type out.txt | find /V "rows affected" > cols.txt
 
  set BG=%GRAY%

  rem ------------- FOR FIELD IN THE TABLE ------------
  for /f "skip=2 tokens=1,2,3 delims= " %%c in (cols.txt) do (
    echo ^<tr bgcolor="!BG!"^> >> %output%
    echo ^<td^>%%c^</td^>      >> %output%
    echo ^<td^>%%d^</td^>      >> %output%
    echo ^<td^>%%e^</td^>      >> %output%
    echo ^<td^> add notes ^</td^> >> %output%
    echo ^<td^>^<pre^> >> %output%

    set col_name=%%c

    rem ------- Should we show DISTINCT -----------------
    if not '!col_name:~-2!'=='Id' (
      set NO_OUTPUT=1
      call :select "count%O%*%C% from %O%select distinct%O%%%c%C% from %%b%C% t"
      set count=!select!

      if !count! LSS 41 (
        if !count! GTR 0 (
          set NO_OUTPUT=1
          rem -- Reminder: I have to use O and C because for loops don't share parens well          
          call :select "cast%O%%O%count%O%*%C% * 100.0 / !rows!.0%C% as decimal%O%4,1%C%%C%, %%c from %%b group by %%c" 
          type out.txt | find /V "---" | find /V "rows affected" | find /V "%%c" >> %output%
        ) ELSE (
          echo NO DATA >> %output%
        )
      ) ELSE (
         echo !count! unique values >> %output%
      ) 
    ) ELSE (
    rem echo Id field: Not checked >> %output%
    )

    echo ^</pre^>^</td^> >> %output%
    echo ^</tr^> >> %output%  

    rem -------- Flip Every Row Color ------------
    if !BG!==!WHITE! (
       set BG=!GRAY!
    ) ELSE (
       set BG=!WHITE!
    )

  )
  echo ^</table^> >> %output%
)


rem ------------- CLEAN UP --------------
echo ^</body^>^</HTML^> >> %output%
echo.
echo Results are in %output%
echo.
echo As a final manul step, you may want to use notepad to edit %output% and 
echo       replace all three or four space sequences with nothing; this will
echo       trim all unwanted spaces returned from all the database queries.
echo       E.g., search and relpace four spaces with nothing

echo Finished:
time /t
endlocal

goto :EOF


:select

if exist %1 copy %1 select.sql > nul
if exist %1 goto use_file

set cmd=%1
set cmd=%cmd:"=%
echo select %cmd% > select.sql

if (%NO_OUTPUT%)==(1) goto run_cmd

echo select %cmd%


:use_file

if (%NO_OUTPUT%)==(1) goto run_cmd


:run_cmd
sqlcmd -S %SERVER% -U %USER% -P %PW% -d %TABLE% -i select.sql > out.txt


call :GetData

if (%NO_OUTPUT%)==(1) set NO_OUTPUT=0&goto :EOF
type out.txt | find /V "---------------"

goto :EOF


:GetData
rem
rem Pre: out.txt contains data from an SQL statement,
rem Post: when this script is done, you can access %select% to get the first line of that data
rem
set sel_out=" "
rem --- third line of the output has our output
for /f "skip=2" %%a in (out.txt) do (
  set select=%%a
  set sel_out=%%a
  goto leave
)

:leave