Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Other Development Tools
 View database and tables

Author  Topic 

noblemfd
Starting Member

38 Posts

Posted - 2012-06-28 : 18:26:59
am developing an application with VB6 and MSSQL 2000.
1. I want to create a form so that when the form loads, it will display all the database in the MSSQL 2000 and also the tables in each of the database.
2. I want to use ListBox to display all the database, and use ListView to display the tables.The ListView should have checkbox.
3. It should be that when a database is clicked on the ListBox, it should display the corresponding tables in that database on the ListView.
4. Then I should be able to clean up (empty the table) any of the selected table.

Please help me out.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-28 : 18:39:55
Why reinvent the wheel? Why not just have them use existing client tools?

And why are you developing on ancient technology?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

noblemfd
Starting Member

38 Posts

Posted - 2012-06-28 : 19:21:20
Thats what my organization is using

quote:
Originally posted by tkizer

Why reinvent the wheel? Why not just have them use existing client tools?

And why are you developing on ancient technology?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Go to Top of Page

sidestepper
Starting Member

3 Posts

Posted - 2012-08-09 : 21:48:06
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!
Go to Top of Page

Webtalk
Starting Member

2 Posts

Posted - 2012-10-11 : 00:49:54
Thanks, I was searching all over the web for this functionality!

unspammed
Go to Top of Page

noblemfd
Starting Member

38 Posts

Posted - 2013-05-27 : 22:31:28
What I mean is User Defined databas(e.g. aslCadbury) and tables(e.g. tblEmployee, tblCustomer, tblInvoices), not system database.


quote:
Originally posted by sidestepper

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!

Go to Top of Page

noblemfd
Starting Member

38 Posts

Posted - 2013-05-27 : 22:33:42
What I mean is User Defined databas(e.g. aslCadbury) and tables(e.g. tblEmployee, tblCustomer, tblInvoices), not system database.


quote:
Originally posted by sidestepper

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!

Go to Top of Page
   

- Advertisement -