How to find values that contains only 0's and any other digit for example 000000001 or 0000010001 or 010101 or 0002 or 02020 or 0090 etc.in SQL ?

You can use ISNUMERIC() function

SELECT ISNUMERIC(0000004);

This will return 1

SELECT ISNUMERIC('A');

This will return 0

So if you want to select all columns that are numeric only you can use this logic:

select *
from test
where ISNUMERIC(colA) = 1

Or you can use TRY_CAST() function:

select *
from test
where try_cast(colA as int) is not null