Not so certain about using VB, but I've done this sort of thing for our web based tools at my company with C#. But most of the magic is in making a stored procedure(Which we use SQL 2008, but it may work in 2000).
I know this post is from June, but if you're still looking, maybe this stored procedure will help:
CREATE PROCEDURE [dbo].[DescribeDatabase]
@databaseName varchar(50),
@objectId varchar(50) = null,
@objectName varchar(50) = null,
@returnDataRows int = null
AS
BEGIN
declare @exists int = (
select COUNT(*) from master..sysdatabases
where name = @databaseName )
if(@exists = 1)
begin
declare @sql varchar(1000)
if(@objectId is not null)
begin
create table #tempTables ([name] varchar(50), [object_id] varchar(50))
set @sql = 'insert into #tempTables ([name], [object_id]) select [name], [object_id] from ' + @databaseName + '.sys.tables'
execute (@sql)
declare @tableExists int = (
select COUNT(*) from #tempTables where [object_id] = @objectId )
if(@tableExists = 1)
begin
set @sql =
'USE ' + @databaseName + ' ' +
'SELECT sysobjects.name [TableName], ' +
' syscolumns.name [ColumnName], ' +
' systypes.name [ColumnType], ' +
' ( ' +
' CASE ' +
' WHEN syscolumns.length = -1 ' +
' THEN systypes.length ' +
' ELSE syscolumns.length ' +
' END ' +
' ) as [ColumnLength], ' +
' ( ' +
' CASE ' +
' WHEN syscolumns.length = -1 ' +
' THEN ''1'' ' +
' ELSE ''0'' ' +
' END ' +
' ) as [IsMax], ' +
' syscolumns.length [ColumnLength], ' +
' ( ' +
' CASE ' +
' WHEN syscolumns.[id] in ( ' +
' select [id] ' +
' from sysobjects ' +
' where name = ''' + @objectName + ''' ) and ' +
' colid in ( ' +
' select SIK.colid ' +
' from sysindexkeys SIK ' +
' JOIN sysobjects SYSO on SIK.[id] = SYSO.[id] ' +
' where SIK.indid = 1 and SYSO.[name] = ''' + @objectName + ''' ) ' +
' THEN ''1'' ' +
' ELSE ''0'' ' +
' END ' +
' ) as [IsPrimaryKey] ' +
'FROM sysobjects ' +
' JOIN syscolumns ON sysobjects.id = syscolumns.id ' +
' JOIN systypes ON syscolumns.xtype=systypes.xtype ' +
'WHERE sysobjects.xtype=''U'' and sysobjects.id = ' + @objectId + ' and systypes.name <> ''sysname'' ' +
'ORDER BY sysobjects.name,syscolumns.colid'
execute (@sql)
end
drop table #tempTables
-- Asking for set of data rows as well
if(@returnDataRows is not null and @objectName is not null)
begin
set @sql =
'USE ' + @databaseName + ' ' +
'SELECT * ' +
'FROM ' + @databaseName + '..' + @objectName
execute (@sql)
end
end
else
begin
set @sql = 'select * from ' + @databaseName + '.sys.tables order by name'
execute (@sql)
end
end
END
Now, to use it, first call with just the database name you want:
exec dbo.DescribeDatabase @databaseName='MyDatabase'
From that list you can then have your application capture the object id's and names, and use them to call the procedure again with different arguments to drill down into the various tables:
exec dbo.DescribeDatabase @databaseName='MyDatabase', @objectId='17492234', @objectName='CustomersTable'
Hope this helps!
View database and tables
Posted - 06/28/2012 : 18:26:59
