SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 View database and tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

noblemfd
Starting Member

Nigeria
36 Posts

Posted - 06/28/2012 :  18:26:59  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 06/28/2012 :  18:39:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Nigeria
36 Posts

Posted - 06/28/2012 :  19:21:20  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 08/09/2012 :  21:48:06  Show Profile  Reply with Quote
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 - 10/11/2012 :  00:49:54  Show Profile  Reply with Quote
Thanks, I was searching all over the web for this functionality!

unspammed
Go to Top of Page

noblemfd
Starting Member

Nigeria
36 Posts

Posted - 05/27/2013 :  22:31:28  Show Profile  Reply with Quote
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

Nigeria
36 Posts

Posted - 05/27/2013 :  22:33:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000