There are two options:

• user-defined scalar function
• properties table

In Oracle, you can do it within Packages - the closest SQL Server has is Assemblies...

I don't think there are any defined constants but you could define them yourself by storing the values in a table or by using a scalar valued function.

Table

Setup a table that has three columns: TypeName, Max and Min. That way you only have to populate them once.

Scalar Valued Function

Alternatively you could use scalar valued functions GetMaxInt() for example (see this StackOverflow answer for a real example.

You can find all the max/min values here: http://msdn.microsoft.com/en-us/library/ms187752.aspx

Avoid Scalar-Functions like the plague:
Scalar UDF Performance Problem

That being said, I wouldn't use the 3-Column table another person suggested.
This would cause implicit conversions just about everywhere you'd use it.
You'd also have to join to the table multiple times if you needed to use it for more than one type.

Instead have a column for each Min and Max of each Data Type (defined using it's own data type) and call those directly to compare to.

Example:

SELECT *
  FROM SomeTable as ST
  CROSS JOIN TypeRange as TR
  WHERE ST.MyNumber BETWEEN TR.IntMin  AND TR.IntMax

On previous versions I would sometimes use

SELECT 0x7fffffff + 0         /*  2147483647 */
SELECT 0x80000000 + 0         /* -2147483648 */

to get the value as I remember the patterns in hex but not decimal

SQL Server 2022 supports bit shift operators so you could also do

SELECT -1 >> 1          /*  2147483647 */
SELECT -1 << 31         /* -2147483648 */