How to get ColumnNames, datatypes and length

,

This is a followup to the script submitted by srallapalli. One can return a record set of all tables in a database using the following:

USE MyDatabase

SELECT so.name AS TableName, sc.name AS ColumnName,  st.name AS ColumnDatatype,  convert(int, sc.length) AS ColumnLength FROM syscolumns sc, sysobjects so , systypes st WHERE so.type='U' AND OBJECTPROPERTY(so.id,'ismsshipped') = 0 AND sc.id=so.id AND st.xusertype=sc.xusertype ORDER BY so.name

To get a result in more of a data dictionary format with the table name shown once only use a stored procedure as follows:

DECLARE @allnames table (id int IDENTITY, name varchar(64))
DECLARE @dd table (Name varchar(64), ColumnDatatype  varchar(64), ColumnLength varchar(64))
DECLARE @tablename varchar(64), @maxrow int, @i int, @inmaxrow int, @ini int
set @i = 1
SET NOCOUNT ON
INSERT @allnames
SELECT name
FROM sysobjects 
WHERE type = 'U' AND 
OBJECTPROPERTY(id,'ismsshipped') = 0
ORDER BY name
SELECT @maxrow=@@rowcount
WHILE @i<= @maxrow 
BEGIN
SELECT @tablename=name FROM @allnames WHERE id=@i
INSERT @dd ([Name], [ColumnDatatype], [ColumnLength]) VALUES (@tablename, '*', '*')
INSERT @dd
SELECT sc.name AS Name,  st.name AS ColumnDatatype,  convert(int, sc.length) AS ColumnLength
FROM syscolumns sc, sysobjects so , systypes st
WHERE so.type='U' AND so.name=@tablename AND sc.id=so.id AND st.xusertype=sc.xusertype
SET @i = @i + 1   
END
SELECT * FROM @dd
SET NOCOUNT OFF

Rate

Share

Share

Rate