SQL Server: Show Tables, Describe

Create proc Describe @tableName varchar(30)
as
begin
set nocount on

select
column_name,
data_type + ' ' +
(case data_type
when 'char'
then '(' + convert (varchar(6),character_maximum_length) + ')'
when 'varchar'
then '(' + convert (varchar(6),character_maximum_length) + ')'
when 'nchar'
then '(' + convert (varchar(6),character_maximum_length) + ')'
when 'nvarchar'
then '(' + convert (varchar(6),character_maximum_length) + ')'
else ' ' end) as data_type,
(case is_nullable
when 'No' then 'NOT NULL' else 'NULL' END) AS NULLABLE
FROM information_schema.columns
WHERE table_name = @tableName
Order by Ordinal_Position asc

set nocount off
end
go

Base Tables

select *
FROM information_schema.tables

select *
FROM information_schema.columns

Link Posted by Tim at May 6, 2005 09:48 AM | TrackBack