How to find information about your database tables

The following 3 scripts will give you information about your database tables:

#1:

DECLARE @tablename varchar(100)
SET @tablename = N'TestTable'
 
SELECT
tbl.*,
clmns.*,
usrt.*,
baset.*,
clmns.name AS [Name],
usrt.name AS [DataType],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN
clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and
baset.user_type_id = baset.system_type_id
WHERE
(tbl.name=@tablename and SCHEMA_NAME(tbl.schema_id)=N'dbo')
ORDER BY
clmns.column_id ASC

#2:

select * from information_schema.columns
where table_name = 'TestTable'

#3:

sp_columns 'TestTable'

 

Comments are closed.