sys.modules vs INFORMATION_SCHEMA.ROUTINES

 One thing I have to remember is that INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION only returns 4000 characters, which can mean that a search could return a false negative. I'm going to let this article speak for me on the deets.

How much space does all this take up?

 Good posts on how to query the database to see how much space is used (specifically by tables, but also other types).

I like this 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

But some other good ways were presented also, such as SSMS's built-in report, and a loop over 'sp_spaceused' with 'sp_msforeachtable'.

SQL XML String Splitter

I don't use this method often enough to memorize it, so I'm making a bookmark for MSSQL Tips article on how to implement. One trick to it is that I found that the ampersand character "&" will break the XML parsing. The workaround to this is substituting another rarely found character for it before parsing, then reverse the substitution afterwards. This StackOverflow post goes into some detail about this.

Check out this site for detailed analysis of how XML string parsing compares to iterative method.

And finally, to raise this even higher, Brent Ozar has a great article about using the STRING_SPLIT function in SQL 2016 and higher, which simplifies this process immensely (although getting it to output parsed element numbers adds a layer of complexity). Check out this StackOverflow post on how to add those element numbers. Beware that although they will be consecutive, they may not start with "1", which means another pass to recalibrate.

MS-DOS Batch File Copy with Date-Stamped Name

 I created a batch file to copy the compiled exe of a Winform app, and then backup the source code to an archive folder named with the date/time stamp:

@echo off

cls

echo Date format = %date%

echo.

echo Time format = %time%

echo.

set Timestamp=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

set folder=_versions\wiki%Timestamp%\

xcopy app %folder% /s /e 

copy app\Wiki\Wiki\obj\Debug\Wiki.exe _release\Wiki.exe

I got a lot of help for this from this StackExchange entry. I found this Steve Jansen article useful also.