In this post, we will discuss writing a transact SQL query to list all the tables, views, store procedures in a SQL server database and it is also a very common SQL server interview question.
Within SQL server management studio, We can use Object Explorer to list all the tables that belong to a specific database.
For example, if we have to find the tables that belong to the Demo database, we can simply expand the database within object explorer and then expand the tables folder.
And then you can find the list of tables that belong to the Demo database
On the other hand, if I have to write a query to achieve the same, then we can make use of three system views.
We can either SYSOBJECTS, SYS.TABLES or INFORMATION_SCHEMA.TABLES views.
The SYSOBJECTS view is available and SQL Server 2000, 2005, and 2008.
Whereas SYS.TABLES and INFORMATION_SCHEMA.TABLES Views are available in SQL Server 2005 and 2008 and later version only, they are not available in SQL server 2000.
Now let’s see how to use SYSOBJECTS view to retrieve all the tables that are present in the Demo database.
Now, when I execute the below query, I get all the objects that are present in the Demo database. SQL Server can have different types of objects like tables, views, function stored procedures.
So SYSOBJECTS view contains all those objects. Now, if I just want the tables, then I filter on Xtype column.
When I execute the query, notice that I get the tables that are present in the Demo database.
Now Xtype, what does ‘U’ mean? ‘U’means user table. So where do I have this list? Actually, you can visit MSDN link for all possible Xtype column values and what they represent.
fn stands for a scalar function So if you want all scalar functions then you simply, filter on that
Select * from [SYSOBJECTS] where XTYPE=’fn’
if I want stored procedures, the Xtype for that is sp, I get all the stored procedures. Similarly, if you want just views then replace XTYPE with V.
Now, if I want to know what are the different types of objects are available in the Demo database, I can find out that by issuing a distinct query.
Select Distinct XTYPE from [SYSOBJECTS]
Now let’s see how to use the SYS.TABLES.
You simply say Select * from SYS.TABLES. That’s going to return all the tables in our Demo database.
Select * from SYS.TABLES
Similarly, if you want views, you can simply use, SYS.views. And if you want procedures, you simply use SYS.procedures. So I see all the stored procedures within the Demo database.
Now, let us see how to use INFORMATION_SCHEMA.TABLES View,
Select * from INFORMATION_SCHEMA.TABLES is going to give us all the tables as well as views
So if you want just the tables, then filter on the table type column.
Similarly, if you want just the views, you can use INFORMATION_SCHEMA.VIEWS.
Select * from INFORMATION_SCHEMA.VIEWS
Now, if you want stored procedures, you can use INFORMATION_SCHEMA.ROUTINES
Select * from INFORMATION_SCHEMA.ROUTINES
Thank you for reading. Have a great day.
The post How to Get the list of all Tables, Views, Stored procedures appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- c# too many arguments specified in a procedure or a function
- Display Missing Rows from Tables of MSSQL
- SQL Server output as XML all on one line
- Dynamic dates in SQL Server SQL statement
- only date and only time in same column - sql server
- SQL server cleaning up databases, transaction logs?
- SQL Server | Finding out count and category
- Hash password with salt in SQL Server
- show different data from two tables
- Get the content of range of cells
- What does a query return in SQL Server?
- Azure SQL Slow Query when targeting an index
- circular foreign key. How do i handle them?
- Get a Value frm SQL Server in timer c#
- Error inserting data using SqlBulkCopy
- Entity Framework - convert LINQ method chain with SelectMany to query syntax
- Load data from two tables in the same query
- Update Table/T-SQL
- SQL Replace() function on a select or variable
- SQL Server: not like (<>) hides null values
- NullReferenceException when Adding a SqlParameter to a SqlParameterCollection
- Resolve Expression from Node API Endpoint (Boolean)
- How can return identity column value from table and insert other table in SQL Server?
- Select distinct value and bring only the latest one
- Left Joining Table with Sum - Causing Issues
- bcp sql command connection error
- Query that Counts records with a WHERE clause
- T-SQL: How can I compare two variables of type XML when length > VarChar(MAX)?
- Does rowversion/timestamp affects performance significantly?
- Create trip number when a flag value changes
- Leaving Change Data Capture cleanup disabled?
- Must declare the scalar variable "" that is already declared
- Why does the presence of primary key on the table significantly enhance the performance of column-store indexes?
- Compare Features between client side sorting and SQL Server sorting
- Select from table 1 unless there is a relationship in 2 other tables
- String or binary data would be truncated.?
- Visual Studio 2013 incompatibility with MS SQL Server 2014
- What happens when I drop a clustered primary key in SQL 2005
- WHERE Clause Performance While Querying an Indexed View
- SQL Server 2005 Express - error after cloning machine
- Permissions error when attaching a database: "Cannot access the specified path"
- SqlBulkCopy not copying any data
- Concatenating Column Values into a Comma-Separated List
- how to add single quotation as a string in a query?
- Updating Replicated Database
- Return records that only have a certain status from a select query that includes multiple joins
- Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator
- Responsiveness/performance IIS web site
- How to index all foreign keys in MS SQL?
- Get First Single matched element or First if there's no match?
- Concat groups in SQL Server
- Storing constants for use in SQL and C#
- In a junction table, should I use a Primary key and a unique constraint, or a compound/composite key?
- How to delete data in 3 relation table in laravel 5.7
- SQL Server - group by with row number - Gaps and Islands
- Is there a way to make recursive SQL queries without using a CTE?
- Why can't I use alias in a count(*) "column" and reference it in a having clause?
- T-SQL Procedure Custom RAISERROR Always Shows As Number 18054 In VS2010 When Using EF4
- Error while joining two tables
- INSERT OVER statement?
- SQL query to copy from some columns of a table to one column in another table
- Pivot confuses me. Can someone help me understand using my example?
- Counting distinct members based on number of months in duration of time
- Benefits of async SqlClient methods
- Recurring event Reminder using asp.net and Ms SQL
- Error in varchar to numeric conversion with sub query
- Volume of store sql
- SQL query to merge the data based on boolean column
- Optimization of a T-SQL Query with probability for selection
- SQL Server hosting only offers 1GB databases. How do I split my data up?
- How to remove from DateTime variable hours, minutes, seconds and other parts in SSIS
- Unable to enumerate databases on SQL Server from PowerShell
- EF Core custom results from stored procedure
- Rounding UP in SQL Server?
- Can cumulative data for time-bands be calculated using SQL?
- Error SQL0: The reference to external elements from the source named 'MyDatabase.dacpac' could not be resolved, because no such source is loaded
- Combine the list of intervals from two tables
- how to have dynamically create & alter in the sql script?
- SQL UPDATE by groups
- How to summarize the data in sql query
- sql server round
- Rolling back the whole procedure (all statements)
- SQL Server Nested Case when within select
- How to check if there is a table in sql server or not using c# code?
- How to get a specific part from a string in SQL
- Read data from xml file T-sql
- Sum a column and get the first row in Transact Sql
- Difference between two dates returned in hours,minutes,seconds
- How to pass byte from C# as string to SQL Server stored procedure and convert into varbinary(MAX)
- STRING_ESCAPE json forward slash
- How to get the average of a DATEDIFF()?
- How to Find Current Directory of itself with a TSQL Script File In SQL Server
- Using IN correctly with SQL
- SQL Server - break down a payment correctly
- Written transfer script for DataBase data and File copying
- SQL Server 2008 error - cannot allocate space for object
- SQL - Showing time passed since specific record created (new column)
- Error - "Invalid attempt to read when no data is present." while using SQLDataReader even if data is present
- Create table in SQL Server using union
- Convert all NCHAR columns to NVARCHAR columns in a database or table
- Building dynamic SQL command - Incorrect syntax error
- How to recover table after drop it in SQL Server
- intermittent problem occurring with a table that is only used for selection
- Getting subquery error using string_agg function
- How to replace row value in SQL Server?
- ASP.NET DetailsView Update doesn't work
- How to use output parameter with other columns names in SQL Server
- Why are my variables do not change their values?
- Update data with elements from excel using stored procedure in SQL Server
- Joining and grouping to equate on two tables
- SQL Server 2008 generate script wizard gives me a script that results in "unclosed quotation marks"
- SQL Server, Converting NTEXT to NVARCHAR(MAX)
- SQL Server 2016 - Build logical Hierarchy into JSON structure from single table
- Crystal Reports SQL query give strange results - can someone explain why?
- SecurityException error when calling CLR function
- Why is my Web Application not using AppPoolIdentity to log in to SQL Server on same machine?
- Can several Inserts be executed using SqlDataReader.NextResult() the same way several Selects can be?
- Running SQL script using C# code ASP.NET Core
- How to return or display distinct values only from a specific column using EF Core?
- tsql identity insert without column name list
- How do I find out which line of which stored procedure an error occurred on?
- Does stored procedure execute when ASP.NET application which called it has timed out?
- Improving SQL query generated by LINQ to EF
- Entity Framework make set of columns unique
- SELECT nearest under or equal to X based on SUM amount SQL Server
- node.js: Creating a transaction that covers calls to more than one repository
- Merge into throws foreign key conflict
- "Incorrect syntax" error when running SQL query
- How do I execute db.SaveChanges() then update database again?
- why schema is not created in mysql?
- how to store regions in a database field
- Different results from executing a stored procedure using exec sp_executesql compared to EXEC
- T-SQL Dollar Sign In Expressions
- Hibernate tools and SQL server with integrated security
- Check if string is found in one of multiple columns in SQL
- ColumnStore Index Scan is not happening in batch mode
- Can't connect to EC2 SQL Server unless I open to all IPs
- SQL Bulk Insert Foreign Key Conflict
- Select existing data from database to create test data
- How to get a bigint to a long object property from SQL Server using Dapper?
- Turn one column into multiple based on index ranges
- Adding Content Database with STSADM, but failing to connect to SQL Server
- SQL Server Service Broker - Message Timeout
- How to select times from first table which are not available in the second table?
- Select distinct + select top to merge multiple rows
- SQL SERVER: Export Query RESULT as JSON Object
- Replace in SQL Query
- sql max function usage
- How to read SQL Server Report history programatically?
- SQL Server IN vs. EXISTS Performance