How to read a list of files in a directory

create table my_files ([filename] varchar(1000), depth int, file_flag int)

INSERT INTO my_files
EXEC xp_dirtree 'C:\MyDir',1,1

select [filename] from my_files
where file_flag = 1
and [filename] like '%.txt'

Typically this would then be used in a cursor to execute SQL commands using the files or filenames.
However, be aware that xp_dirtree is an undocument/unsupported interface, and might not be available in future releases of SQL Server.

SQL can't average two large integers

SQL is unable to average two integers, presumably because their sum is greater than maxint:-

select
avg(col)
from (
select 2147483647 as col
union all
select 2147483647
) t


fails with:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Where 2147483647 is the max value of an int datatype.

SQL Gotcha: convert-to-varchar defaults to only 30 characters

Converting a varchar to a varchar trims to 30-characters:-

select '0123456789112345678921234567893123456789ExtraStuff'

Returns:-

0123456789112345678921234567893123456789ExtraStuff

But:-

select convert(varchar,'0123456789112345678921234567893123456789ExtraStuff')

Returns:-

012345678911234567892123456789

Parsing Visual Source Safe keywords embedded in Stored Procedures

When creating a stored procedure, if you place Visual Source Safe keywords inside the 'CREATE PROCEDURE' definition the keywords get stored in the SQL Server database along with the code in the syscomments system table. This is used to regenerate the original SQL code when you select 'script stored procedure' from Object Explorer.

The following code can be used to parse Visual Source Safe keywords embedded in Stored Procedures in the SQL Server Database.

SELECT

OBJECT_NAME(id),

substring([text],patindex('%$Revision:%',[text]),16),

substring([text],patindex('%$Archive:%',[text]),patindex('%$Revision:%',[text])-patindex('%$Archive:%',[text]))

FROM syscomments

WHERE [text] LIKE '%$Revision:%'

AND OBJECTPROPERTY(id, 'IsProcedure') = 1

order by OBJECT_NAME(id)

Getting the SQL Server Version

Useful SQL for getting your SQL Server version information:-


SELECT
SERVERPROPERTY('productversion'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
Select @@VERSION