From the POWER documentation:
Syntax
POWER ( float_expression , y )
Arguments
float_expression
Is an expression of type float or of a type that can be implicitly converted to float.
y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Return Types
Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).
The first input is implicitly cast to float if necessary.
The internal calculation is performed using float arithmetic by the standard C Runtime Library (CRT) function pow.
The float output from pow is then cast back to the type of the left hand operand (implied to be numeric(3,1) when you use the literal value 10.0).
Using an explicit float works fine in your case:
SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);
An exact result for 1038 cannot be stored in a SQL Server decimal/numeric because it would require 39 digits of precision (1 followed by 38 zeros). The maximum precision is 38.
If the queries are in stored procedures, or you can get them into a temporary database as stored procedures then the system SP sp_depends and related catalog views will help. At the very least they will narrow the search.
If the queries are in files a bit of Powershell will generate SPs in no time.
If they're embedded in the application or, heaven forfend, dynamically generated at run time running an application regression test, capturing the submitted SQL and proceeding as above may work.
We have had success with a .Net SQL parsing library. Its name eludes me just now. Should I find it I'll edit it into this answer. You'll still have to pull the SQL from your application, of course.
Best Answer
There are lots of cases that @AmmarR's solution doesn't handle -
ROWVERSION/TIMESTAMP, computed columns, columns with check constraints, foreign keys,UNIQUEIDENTIFIERcolumns that default toNEWSEQUENTIALID(), date/time columns that default toGETDATE(), sparse columns, etc. etc.Why reinvent the wheel? There are tools out there that can generate data for you and they've probably already thought about all of these restrictions. For example DTM Data Generator (not free, have not tested it) and Red-Gate SQL Data Generator (not free, have tested it and recommend it - also reviewed here).