Just the other day I was asked by a Business Line Manager about the usage patterns of tables in a database that I never seen before. I was lucky that the version of the database was 2005 and Dynamic Management Views existed that stored the requirement information. Today, I am going to review a simple SELECT query that will retrieve the information that we want. The first requirement is to list the schema names that the user tables belong to. We can obtain this information by looking at the sys.schemas…
Tag: sys.objects
Database Files & Objects – Part 4
Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be investigating how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on functions, stored procedures, triggers and views in this article. Let’s use the USE statement to select the sample database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Which database to use. USE [MATH] go </span> |
VIEWS – MSDN QUOTE: “A view…
Database Files & Objects – Part 3
Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be reviewing how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on tables, columns, indexes, and constraints in this article. Let’s use the USE statement to select the sample database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Which database to use. USE [MATH] go </span> |
The most basic component of a database…
Searching Stored SQL – Part 4
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Searching Stored SQL – Part 3
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Searching Stored SQL – Part 2
There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…
Searching Stored SQL – Part 1
Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…