Technical Article

Shows Tables for a Given Database from DOS

,

If you are using OSQL or ISQL from DOS querys are really hard to use, because the result is to big for the screen. With this procedure, you can especify from wich column to wich column show the list of tables. For Example: proc_tables master, 3, 10 will show you only a short list of tables order by name.

Soon more scripts like this but for Stored Procedures, backup_devices, columns, triggers, views, etc... I Use then on MSDE

/*
**proc_tables
**Muestra la lista de tablas de usuario en la base seleccionada
**desde un rango especificado hasta otro tambien especificado
**Autor Rodrigo Acosta
**Fecha 07/09/01
*/
CREATE PROCEDURE proc_tables --Se ejecuta desde la master
@dbname varchar(20)=null, --nombre de base en donde buscar
@nro1 int =1, --especifica desde que primer valor listar las tablas
@nro2 int =18  --especifica desde que segundo valor listar las tablas

AS
IF @dbname IS NULL
BEGIN
SET @dbname=(SELECT db_name())
END
SET NOCOUNT ON
DECLARE @select varchar(300) --guarda la sentencia de select

/*
**Si el nombre de la base no existe
**se indica y se listan las bases disponibles
*/
IF NOT EXISTS (
SELECT name
FROM master..sysdatabases
WHERE name=@dbname
)

BEGIN
PRINT 'La base de datos "'+@dbname+'" no existe. mostrando bases existentes...' 
EXEC master..proc_bases 1,18
RETURN 0
END
DECLARE @selectcount varchar(220)
DECLARE @selectdb varchar(300)

SET @selectcount='SELECT "Cantidad de tablas en la base '+@dbname+':"+CONVERT(VARCHAR(20),COUNT(name)) FROM '+@dbname+'..sysobjects WHERE xtype="U"'

EXEC (@selectcount)
PRINT 'Pantallas completas 1-18/19-36/37-54/55-72/73-90/91-108/109-126/127-144...'
SET @selectdb='SELECT o.name,o.uid,u.name 
FROM '+@dbname+'..sysobjects AS o INNER JOIN '+@dbname+'..sysusers AS u ON o.uid=u.uid
 WHERE o.xtype="U" order by o.name'

CREATE TABLE #tablas
(tid tinyint identity(1,1) not null,
name varchar(40),
uid int,
owner varchar(50))
INSERT INTO #tablas
EXEC (@selectdb)
SET @select='SELECT convert(varchar(3),tid)+"."+name AS "Tabla",SUBSTRING(owner,1,20)+"("+CONVERT(VARCHAR(3),uid)+")" AS "Owner(uid)"   FROM #tablas where tid between '+CONVERT(VARCHAR(4),@nro1)+ ' AND '+CONVERT(VARCHAR(4),@nro2)
EXEC (@select)

DROP TABLE #tablas

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating