To get list of all artifact in DB

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

General SQL Server Forums

New to SQL Server Programming

To get list of all artifact in DB

Author

Topic

SadafKhan85
Starting Member

8 Posts

Posted - 2013-07-03 : 07:42:57

Below query will give you all table names from all databases in current server. Currently query is created for tables but this can be modified to get any SQL object from the server, you just need to change the name of data dictionary used in the query (here it is sys.tables).

/** SCRIPT TO GET ALL TABLES FROM A SERVER
CREATED BY : SADAF KHAN
**/
DECLARE @SQL VARCHAR(MAX) -- database name
DECLARE @NAME VARCHAR(100) -- path for backup files
DECLARE db_cursor CURSOR FOR
SELECT DISTINCT NAME
FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN ('master','model','msdb','tempdb')
Execute ('DROP TABLE TMP_ALL_TABLE_NAMES');
Execute ('CREATE TABLE TMP_ALL_TABLE_NAMES( DB_NAME VARCHAR(100), TABLE_NAME VARCHAR(100))');

OPEN DB_CURSOR
FETCH NEXT FROM DB_CURSOR INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT INTO TMP_ALL_TABLE_NAMES SELECT DISTINCT '''+@name+''', NAME FROM '+ @NAME +'.SYS.tables';
Execute(@sql);
FETCH NEXT
FROM DB_CURSOR INTO @NAME
END

CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 08:35:31

This should be posted in script library

A much easier method to avoid cursor is to use system stored procedure sp_msforeachdb for iterating through databases

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 08:36:04

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources