CODEWITHSUNDEEP
c#sql-server
Sathish Kumar
Sathish Kumar

Reputation: 21

SQL- I need script text file need to know which tables and views and stored procedures having that script

I am having script text file that inside having

  1. Create procedure
  2. Create table
  3. alter table
  4. alter procedure
  5. alter view
  6. create view etc..

I need to know views, SPs, tables names and its roles (create or alter) when I run the script.

Example following are text file.

and the result should be following

proc, alter, bal.vendor table, select, manager table, create, employee table, alter, persons

Upvotes: 0

Views: 45

Answers (2)

Ilyes
Ilyes

Reputation: 14928

All those are called Objects, so the only thing may come up to your mind is to use sys.all_objects view which can be found in:

YourDatabase -> Views -> SystemViews

All those objects has a type:

U  -> USER_TABLE
IF -> SQL_INLINE_TABLE_VALUED_FUNCTION
P  ->  SQL_STORED_PROCEDURE
TR ->  SQL_TRIGGER
V  ->   VIEW
...etc

So, you can query this View to get the results you want as:

SELECT object_id,
       name,
       type, 
       type_desc, 
       CASE WHEN create_date = modify_date THEN 'Created' ELSE 'Altered' END AS state, 
       create_date, 
       modify_date
FROM sys.all_objects
WHERE type IN ('U', 'P', 'V'); --Optional you can remove it if you wan all types of objects

Upvotes: 1

Hussein Salman
Hussein Salman

Reputation: 8256

You can use sql query:

SELECT name, type, type_desc, create_date, modify_date
FROM sys.objects
WHERE modify_date > '01-01-2017' -- pass your date
  • name: is the name of the sp, table or view that was modified or created
  • type: indicates type of the object

The objects that were newly created will have the same date for both create_date and modify_date. Use this modified query to show if it was new or modified:

SELECT name, type, type_desc, 
case when  create_date = modify_date then 'Created' Else 'Altered' end
        FROM sys.objects
        WHERE modify_date > '01-01-2017'

enter image description here

Upvotes: 1

Related Questions