Find out which tables and views recently added

Yesterday I added several tables and views. What query to run to get the tables and  views recently created.
It should the ones created on 12/11/2017. Is it possible?
zachvaldezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please use these queries..

TABLES

select * from sys.tables 
WHERE create_date >= DATEADD(d,-1,CAST(getdate() AS DATE))
AND create_date < CAST(GETDATE() AS DATE)
order by create_date desc

Open in new window


VIEWS

select * from sys.views
WHERE create_date >= DATEADD(d,-1,CAST(getdate() AS DATE))
AND create_date < CAST(GETDATE() AS DATE)
order by create_date desc

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zachvaldezAuthor Commented:
Thanks. The reason I asked that is not we are in the process of conversion.

The Situation.

Old Server                                     New Server
Students DB           >>>                 Students DB
                                                         Students DB_Saved
Students DB   is copied to New Server. For some reason, we have been using Students DB Saved for a while.
Therefor there are tables and views for Students DB Saved that are not in Students DB.
Yesterday I copied the tables and views  from Students DB_Saved to Students Db which will be the production in future.
This copy and replacement from Old server to New Server for Students Db can happen several times and therefore, I may need to recreate  the copying of tables and views.
I need a script possibly to execute whenever a copy process is initiated from Old Server to New Server for the Students DB.

The question that boggles my mind also is. Is it possible for the DBA to just replace the tables  for the Students DB that are in Old server that are also in Students DB in the new server and just leave the new  ones in the to New Database so I don't have to repeatedly recreate them. Plus , those tables have data already. Should I script only table structure or should I import/export table script?
0
Pawan KumarDatabase ExpertCommented:
Yes it is possible , ask your DBA to create the linked server between the servers and after that we just need to write the simple queries.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

zachvaldezAuthor Commented:
Can you provide a few details so I can pass your suggestion rationally? Thanks
0
Pawan KumarDatabase ExpertCommented:
To create a linked server -

USE master;  
GO  
EXEC sp_addlinkedserver  
   N'YourNewServeName',  
   N'SQL Server';  
GO

then use like below

SELECT * FROM YourNewServeName.Database.dbo.TableName
1
zachvaldezAuthor Commented:
Just a question for myself. I was thinking of backing up the tables in a local db I would to create but in the process of creating a db
I got this error "CREATING DATABASE permission denied in database 'master'
How would I go around with this?
0
Pawan KumarDatabase ExpertCommented:
Go to SSMS...

right click on your database -> Tasks > Backup...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I got this error "CREATING DATABASE permission denied in database 'master'
How would I go around with this?
If you can't create a database, ask a DBA to create it for you and add your user to the db_owner role in the new database so you can create objects in the database so you can copy the tables from the source database.
0
Mark WillsTopic AdvisorCommented:
>> The question that boggles my mind also is....

That is a very good question. If changes are being made in Students DB Saved, while you might get away with copying (on the new server) to Students DB, you will not be able to simply copy from old server to new server....

This is possibly another question in its own right.

You said : "Therefor there are tables and views for Students DB Saved that are not in Students DB."

Those changes must be scripted, so, each time you copy from production, you will then apply the scripts. Especially when the copy process is going to be repeated several times.

The additional challenge is Data, and if new data elements (rows) are being added, then it isnt a matter of copying DB's.

They need to be merged, or, a decision taken that the (assumed) development group should have their own data import routines to save and recover those new data elements.

It can (and often does) get very tricky and does need a lot of careful prep and planning and coordination.

Best to have a project meeting with your DBA and Dev (or whoever is on the new server) to make sure everyone is on the same page.

I wouldnt go creating DB's just yet, get the group together and plot it out first.
1
zachvaldezAuthor Commented:
Can you provide an example scripting which purpose is copy 3 tables from one server to another?
0
Mark WillsTopic AdvisorCommented:
With a great deal of respect, not until you have had your meeting(s).

because the scripting I was talking about is a lot more than copying tables. It is metadata and (by the sounds of it) possibly data.

For example, what if some of the new things being added was making security more robust. So, for each student, there are new constructs and dependencies or constraints. Copying tables essentially overrides that. Not to mention what can happen with referential integrity.

But if you have all the change scripts, then you could run those scripts over the data and tables etc. I cannot write them for you - the people making the changes need to supply them.

It is very difficult when working over 2 disconnected databases when one of them is growing in a different direction. Think of a simple case when ID's have been added separately to each table. When then trying to import data, those ID's could create conflicts.

So, assuming you want to keep data in the new DB and just need to refresh occasionally, you are probably better off exporting data from production then running an import of that extract into the copy by updating existing data or importing new data.

To give you the correct answer to your question, we need to know more about your production and copy environment and how they need to relate (in a transition) and ultimately, to become the new production server. For exaample, are they developers on the new machine making changes ? If not, then who is making changes ?

And we dont really know what is happening on the new server or why the new server people are using the saved table.

That is also why I mentioned that this could become an (involved) question in its own right.

And depending on in-house expertise, you might end up with some new tools to help the process / automate some aspects (the tools dont cost much if anything) such as SSIS within SQL Server, or licensed tools like https://solutioncenter.apexsql.com/synchronize-sql-server-databases-in-different-remote-sources/
or https://sqldatabasestudio.com/ (look down the bottom for Database compare & synchronization)

Even still, you must have your internal procedures down pact.

It would be irresponsible of me to not point these things out to you and simply go for the points by writing a script. Hope you understand and can appreciate my concerns for your predicament.
1
Mark WillsTopic AdvisorCommented:
I do hope you check out those tools....

Wish you all the very best with your DB migration / server migration plans. Get those developers under control :)
0
zachvaldezAuthor Commented:
Thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.