This is a little post to illustrate how to retrieve all tables in a given schema. This is a handy tool and can be used for various purposes. So lets call this procedure GetSchemaTables, it takes just one variable, the schema name. Remember this procedure has to be defined in the current database to which the schema belongs. Suppose you are developing code that will execute the query for an unknown schema. The schema name will be the value of the variable @schema. You want to build a character string that will be the query, and then execute the query. Remember that beginning or ending a character string requires a single single-quote, but if you want a single quote within a character string, you must “escape” it with a preceding single quote. For example, to concatenate a single quote to a character string would require: + ””. In this four-single-quote string, the outermost two single quotes define the character string, and the inner two single quotes yield a single-quote character. So lets do this,
CREATE PROCEDURE GetSchemaTables (@schema VARCHAR(255))
AS
BEGIN
DECLARE @sql VARCHAR(4000)
SET @sql = ‘SELECT s.name [Schema Name], t.name [Table Name]
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE s.name =’ + ”” + @schema + ””
EXEC (@sql)
END
Lets execute this produce for the HumanResources schema in Adventureworks database. if you don’t know adventureworks, its microsofts sample database for sql server. You can get a copy of the sample database at http://msftdbprodsamples.codeplex.com/
EXEC dbo.Getschematables ‘humanresources’
Schema Name Table Name
——————— ———————-
HumanResources Department
HumanResources Employee
HumanResources EmployeeAddress
HumanResources EmployeeDepartmentHistory
HumanResources EmployeePayHistory
HumanResources JobCandidate
HumanResources Shift
(7 row(s) affected)
The output of this result can be different based on the tables in your humanresources schema in adventures.
Hope this helps in some way of learning TSQL.
Questions and comments welcome.
Jah Bless
