September 30, 2019 at 9:19 pm
I have a database (MyDB) with approximately 500+ standard objects including Tables, Views, Functions, Stored Procedures, Triggers, etc...
I have a table (MyBaseObjects) which lists all the out of the box standard objects
Over time people have created custom objects in MyDB
I'm looking for a script that will compile all the objects in MYDB and compare them to the matching object types in MyBaseObjects.
Where an object exists in MyDB that does NOT exist in MyBaseObjects spool to a csv file the object name and type.
ex:
ABC|Table
EFG|Table
MNO|View
PQR|Function
XYZ|StoreProc
etc...
Thanks,
John
Remember... If you can't control it then don't sweat it!
September 30, 2019 at 10:00 pm
Hi,
As I understood you maintain a table where you keep all the required objects which you name as BaseObjects. Now in the same dB other objects other than the required one you need to extract to a csv.
You can use the below query to list unnecessary objects in your dB-
SELECT NAME AS ObjectName
,schema_name(o.schema_id) AS SchemaName
,type
,o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
AND NOT EXISTS (SELECT 1
FROM MyBaseObjects as myobj Where o.name =myobj.name)
ORDER BY o.NAME
To extract the results in csv you can use OPENROWSET.
October 1, 2019 at 2:24 pm
Thanks,
That worked great!
Thanks,
John
Remember... If you can't control it then don't sweat it!
October 3, 2019 at 12:52 pm
Bit of a dirty cheat, but seeing as they do support the site...
Download Redgate Toolbelt and use database compare. This will not only pick up top level objects such as tables and views but also additional fields, fields in a different order or differences in data types.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply