| Author |
 Topic  |
|
|
noblemfd
Starting Member
Nigeria
21 Posts |
 Posted - 06/28/2012 : 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
USA
35020 Posts |
|
|
noblemfd
Starting Member
Nigeria
21 Posts |
|
|
sidestepper
Starting Member
USA
3 Posts |
Posted - 08/09/2012 : 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 - 10/11/2012 : 00:49:54
|
Thanks, I was searching all over the web for this functionality!
unspammed |
|
|
|
noblemfd
Starting Member
Nigeria
21 Posts |
Posted - 05/27/2013 : 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
Nigeria
21 Posts |
Posted - 05/27/2013 : 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!
|
|
|
| |
Topic |
|
|
|
View database and tables
