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

Share

Share

Rate