Writing sql to show the structure of a table
-
Hello,
I am using Query Analyzer instead of Enterprise Manager.
I need an example of a sql script that shows the structure of the table (ie, col name, size and type).
Thanks
Indy
-
Is there something you're looking for that running an "sp_help" won't give you?
Mark
-
sp_help is exactly what I was looking for.
Thanks
Indy
-
you can also use INFORMATION_SCHEMA.COLUMNS
-
Or you can use this that was posted on the site as an article a little while ago:
Select
'Table Name ' = B.name,
'Column name' = A.name,
'Column No' = colid,
'Type' = type_name (xusertype),
'Length' = convert (int, length),
'Allow Nulls'= case when isnullable = 0 then 'No' else 'Yes' end
From syscolumns A, sysobjects B
Where A.id=B.id and B.type ='U' and A. number=0 and B.Status > 0
AND B.[Name] = YourTableName
Order by b.name, colid
Obviously replace the "YourTableName" for well... yeah... you get it!
-
If you like a table definintion that can be printed on the width of a page and saved as text that can be imported into documentation, I have the following stored procedure that I wrote (with the help of postings on this site). It includes the description you store with each field and the table. Too long of a description or a period in the description creates a line feed so it will fit on a page. You can tailor it to the way you want to see your documentation.
After creating, you can run it from Query Analyzer with results to text and then save the results to a file with a .txt extension.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc usp_Table_Doc
@tblname varchar(50)
as
if @tblname is null begin
raiserror(15250,-1,-1)
return(1)
end
-- validate @tblname
declare @id int,@dbname sysname,@type char(2)
if @tblname is not null begin
select @dbname = parsename(@tblname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@tblname)
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@tblname,@dbname)
return (1)
end
end
declare @tabledesc sql_variant, @tabdes varchar(500),
@headtemp varchar(70), @headlen tinyint, @i tinyint,
@j tinyint
SELECT @tabledesc = value
FROM ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, DEFAULT, DEFAULT)
if @tabledesc is not null
select @tabdes = cast(@tabledesc as varchar(500))
else
select @tabdes = ' '
select @headlen = 88 - (17 + len(@tblname))
select @i = @headlen
if len(@tabdes) > @headlen
begin
while substring(@tabdes,@i,1) <> ' '
select @i = @i - 1
end
select @headtemp = left(@tabdes,@i)
print '========================================================================================'
print '------'
print '------ Table: '+@tblname+ ' - ' + @headtemp
while len(@tabdes) > @headlen
begin
select @tabdes = substring(@tabdes,@i + 1,len(@tabdes))
select @i = @headlen
if len(@tabdes) > @headlen
begin
while substring(@tabdes,@i,1) <> ' '
select @i = @i - 1
end
select @headtemp = left(@tabdes,@i)
print '------'+space(88-6-@headlen)+@headtemp
end
print '------'
declare @sqltext varchar(8000)
select @sqltext=
'declare tblstru_crsr cursor for
select a.name,a.xusertype,a.length,a.xprec,a.xscale,a.isnullable from syscolumns a,sysobjects b
where b.name like '''+@tblname+''' and b.id=a.id order by colid'
exec (@sqltext)
declare @name varchar(21),@xusertype smallint,
@length smallint,@vcLen varchar(4),@xprec tinyint,
@xscale tinyint,@vcSca varchar(3),@isnullable int
declare @namedesc varchar(21),@typedesc varchar(14),
@nulldesc varchar(4),@lendesc varchar(13),
@description sql_variant,@desc varchar(500),
@desctemp varchar(38)
open tblstru_crsr
fetch tblstru_crsr into @name,@xusertype,@length,@xprec,@xscale,@isnullable
set nocount on
print 'Column Name Type Length Scale Null Description'
while @@fetch_status= 0 begin
print '-------------------- ---------- ------ ----- ---- --------------------------------------'
-- right fill with spaces
select @namedesc = @name+space(21-len(@name))
SELECT @typedesc = left(name,12) FROM systypes
WHERE xtype in (SELECT xtype FROM systypes
WHERE xusertype = @xusertype) and xtype = xusertype
-- Proper case and right fill with spaces
select @typedesc = upper(left(@typedesc,1))+substring(@typedesc,2,len(@typedesc))+space(12-len(@typedesc))
-- Right Justify Numbers within fields
if @xprec > 0
begin
select @vcLen = ltrim(str(@xprec))
select @vcLen = space(4-len(@vcLen))+@vcLen
select @vcSca = ltrim(str(@xscale))
select @vcSca = space(3-len(@vcSca))+@vcSca
select @lendesc = @vcLen+' '+@vcSca+' '
end
else
begin
select @vcLen = ltrim(str(@length))
select @vcLen = space(4-len(@vcLen))+@vcLen
select @lendesc = @vcLen+space(9)
end
if @isnullable = 1
select @nulldesc = 'YES '
else
select @nulldesc = 'NO '
SELECT @description = value
FROM ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, N'column', @name)
if @description is not null
select @desc = cast(@description as varchar(500))
else
select @desc = ' '
if len(@desc) > 38
select @i = 39
else
select @i = len(@desc)
begin
while substring(@desc,@i,1) <> ' ' and @i < len(@desc)
select @i = @i - 1
select @j-2 = 1
while substring(@desc,@j,1) <> '.' and
@i > @j-2
select @i = @j-2
end
select @desctemp = left(@desc,@i)
print @namedesc+@typedesc+@lendesc+@nulldesc+@desctemp
while len(@desc) > @i
begin
select @desc = substring(@desc,@i + 1,len(@desc))
if len(@desc) > 38
select @i = 39
else
select @i = len(@desc)
begin
while substring(@desc,@i,1) <> ' ' and @i < len(@desc)
select @i = @i - 1
select @j-2 = 1
while substring(@desc,@j,1) <> '.' and
@i > @j-2
select @i = @j-2
end
select @desctemp = left(@desc,@i)
print space(50)+@desctemp
end
fetch tblstru_crsr into @name,@xusertype,@length,@xprec,@xscale,@isnullable
end
close tblstru_crsr
deallocate tblstru_crsr
print '========================================================================================'
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Ummmm.... press the {f8} function key...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
is it possible to get the structure of a temporary table already in memory?
-
exec tempdb..sp_help #tablename
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply