View database and tables - SQL Server Forums

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

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

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!

Webtalk
Starting Member

2 Posts

Posted - 2012-10-11 : 00:49:54

Thanks, I was searching all over the web for this functionality!

unspammed

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!

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:


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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources