Here is the SQL query that will help you list all the tables and columns along with its data type and length in a particular database.

This query fires query to the system table that stores all the information about the database.

select table_name, column_name, data_type, character_maximum_length, is_nullable from information_schema.columns where table_name in (select name from sysobjects where xtype='U') order by table_name

Hope this helps, enjoy

Because SQL Server provides both functions, there may be some confusion about which is best to use and under what circumstances. CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifier.

CAST is the more ANSI-standard of the two functions, meaning that while it’s more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it’s also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it’s best to use CAST first, unless there is some specific thing that only CONVERT can provide in the work you’re doing.

CAST and CONVERT can also be used in conjunction with each other to achieve certain effects. For instance, a typical way to produce a char variable with the current date would be to use:

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)

(The 102 indicates that the ANSI date format, yy.mm.dd, is to be used.)

However, if you wanted to cast this variable explicitly as a datetime or smalldatetime value for compatibility in a specific database column, you could use:

SELECT CAST(CONVERT(CHAR(10),CURRENT_TIMESTAMP,102) AS DATETIME)

This would return the value yy.mm.dd 00:00:00 (i.e., 12:00AM as the timestamp; the time information from CURRENT_TIMESTAMP would be discarded).