How To Find Out Changes Happened For All Objects
Jul 30, 2013
How i can find out the changes happened in database like modifying functions,table indexes,procedures and adding or removing columns..here in this query
select * from sys.objects
where type IS NOT NULL
and modify_date between '2013-07-21' and '2013-07-29'
but here i am getting created objects list and modifying list.but if i deleted any object it is not showing anything..how can i get the all the changes happened in database between specific dates.
View 3 Replies
ADVERTISEMENT
Jun 5, 2007
I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?
SQL 2005 Enterprise Edition SP 2
Thanks in advance.
Kay
View 1 Replies
View Related
Sep 28, 2006
Hi
I have a SQL Server 2005 database running. When I run some ddl changes, I want to find all the procs/objects that get invalidated because of object not found error.....
Is there any way that I can look up in sysdepends or other tables to find information about this.
Regards
Imtiaz
View 1 Replies
View Related
Apr 24, 2014
I need to find all the invalid objects means which will throw error on execution in a particular DB.
View 1 Replies
View Related
May 5, 2006
-- This stored procedure will let you search through your database
-- to find various objects that contain a particular string.
-- For example, you may want to see all tables and views that contain
-- a particular column.
use master
IF (object_id('sp_FindReferences') IS NOT NULL)
BEGIN
PRINT 'Dropping: sp_FindReferences'
DROP procedure sp_FindReferences
END
PRINT 'Creating: sp_FindReferences'
GO
CREATE PROCEDURE sp_FindReferences
(
@string varchar(1000) = '',
@ShowReferences char(1) = 'N'
)
AS
/****************************************************************************/
/* */
/* TITLE: sp_FindReferences */
/* */
/* DATE: 18 February, 2004 */
/* */
/* AUTHOR: WILLIAM MCEVOY */
/* */
/****************************************************************************/
/* */
/* DESCRIPTION: SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */
/* */
/****************************************************************************/
set nocount on
declare @errnum int ,
@errors char(1) ,
@rowcnt int ,
@output varchar(255)
select @errnum = 0 ,
@errors = 'N' ,
@rowcnt = 0 ,
@output = ''
/****************************************************************************/
/* INPUT DATA VALIDATION */
/****************************************************************************/
/****************************************************************************/
/* M A I N P R O C E S S I N G */
/****************************************************************************/
-- Create temp table to hold results
create table #Results
(
Name varchar(55),
Type varchar(12),
DateCreated datetime,
ProcLine varchar(4000)
)
IF (@ShowReferences = 'N')
BEGIN
insert into #Results
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
ELSE
BEGIN
insert into #Results
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = text
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
IF (@ShowReferences = 'N')
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated
from #Results
order by 2,1
END
ELSE
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated,
ProcLine
from #Results
order by 2,1
END
drop table #Results
GO
IF (object_id('sp_FindReferences') IS NOT NULL)
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO
View 4 Replies
View Related
Nov 8, 2007
I'm trying to identify the objects in master that the role public has select permissions on, but when I run this query, I get 4 results where the default schema is null and the major_id column does not correspond to any records in the sys.all_objects table. Where else can I look to find what objects these are. DBO is listed as the grantor.
I appreciate your help.
SELECT *
FROM SYS.DATABASE_PERMISSIONS P,
SYS.DATABASE_PRINCIPALS R
WHERE P.GRANTEE_PRINCIPAL_ID=R.PRINCIPAL_ID and
permission_name='SELECT' and class_desc='OBJECT_OR_COLUMN' and
r.name='public'
order by r.name desc
View 9 Replies
View Related
Apr 1, 2008
Hi All,
I have an assignment to change names of the SPs that are not following Standard naming convention in all databases. For this first I need to find out all the SPs that are not following naming convention. Can anybody help me in finding out the objects that are not following standard naming conventions. It is first step in completing this assignment. If anybody deal with this type of task before please guide me.
Hope any one of you will reply me with more information......
Thanks,
Pradeep
View 9 Replies
View Related
Jan 31, 2007
Hi Frens,
Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?
Thanks a lot for your time.
Regards,
-Ram.
View 11 Replies
View Related
Jul 25, 2014
How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..
View 4 Replies
View Related
Jun 22, 2015
I am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.
I am running the following query
select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb
[Code] ....
Following are the results:-
DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....
Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.
View 0 Replies
View Related
Jan 7, 2004
Hi everybody!
Something strange happened to one of my SQL Databases.
One of my User Defined Funcitions has lost ALL its roles permissions!
I created this UDF a long time ago and I assigned permissions to some specific Roles to run the UDF. It worked fine until today. All of a sudden there were no permissions for any role for that UDF. I am the only Admin for the DB.
I reassigned the roles permissions and it seems to be working fine now.
How could this happen ? has anyone here experienced something like this before ?
Thanks!
View 14 Replies
View Related
Mar 5, 2004
http://www.dbforums.com/showthread.php?postid=3636029#post3636029
View 2 Replies
View Related
Mar 18, 2004
Rules...
"Clean up your room, stand up straight, pick up your feet, take it like a man, be nice to your sister, don't mix wine and beer, Oh, and don't drive on the railroad tracks..."
http://www.dbforums.com/t989246.html
View 2 Replies
View Related
Jun 8, 2007
Hi!Weird thing today!I opened Visual Studio 2005 and I can only see themastermodelmsdbtempdbSystem DatabasesWith the SQL Server management studio I can still see all mydevelopment DB, but NOT fron Visual Studio.There has been some maintenance on the machine done by tha servicedepartment, but they don't know what to say.Anyone has an Idea of what happened?Thank you!Piero
View 2 Replies
View Related
Sep 16, 1998
I have obtained control of yet another server. When I open Enterprise Manager, I don`t get the traffic lights on the new server to light up. Also the sqlexec (wheels) do not even appear. The server and I are on different workgroups, but another user, who is also in a different workgroup from the server gets everything to show fine. What happened? My other 3 servers appear just fine. What is my lack of connectivity?
View 1 Replies
View Related
Oct 11, 2005
Hey, who took the sticky down?
View 2 Replies
View Related
Nov 20, 1998
In the SQL Object manager tools for sql server 4.21 there was a gui interface for running BCP where you could select tables and fields etc.
I haven't beem able to find abnything equivalent in sql 6.5 enterprise manager. Am I missing someting, or was this dropped?
Thanks.
Jerry Dunn
ViaHealth
jerry.dunn@viahealth.org
View 1 Replies
View Related
Sep 13, 2007
In SQL Server 2000 you can monitor active processes by looking at the sql_handle column in sysprocesses. In SQL Server 2005 BOL says sql_handle ...
Represents the currently executing batch or object.
In SQL 2000 a non-zero value in sql_handle indicated an active process. You could not simply rely on the status column to check for an actively running process. This is not the case in 2005. I ran a profiler trace to confirm that just because sql_handle reports a non-zero value does not mean the corresponding process is active. Does anyone know why this has changed and how we are now to interpret sql_handle in 2005? Why are non-zero entries reported for processes that are not currently running?
Thanks, Dave
View 1 Replies
View Related
Nov 11, 2007
Ive got a table of notes people have created, with a field called "timecreated" which has a default value of "GETDATE()" Im trying to write an SQL statement that will count up all of the notes that people have created today/ yesturday etc. i could do it if the timecreated value was a "short date string" styled date, but its set up like : 11/11/2007 18:51:46 is there way of converting it before counting? if theres a simple way of doing this i would appricate any help thanks John
View 7 Replies
View Related
Nov 1, 2000
I have W2K Adv, SQL 7 Enterprise in a workgroup with mixed security. The SQL installation is an almost entirely default install.
At the console, in SQL Enterprise Manager, when I select open a database, open tables, select a table, open table, and select all rows the system displays "an unexpected error happened during this operation". As far as I know this has never worked on this installation.
The error occurs with every logon account (including sa and local administrator accounts), both the "all rows or top row" options, every table, every database (including the customers database and the Northwind database).
I have tried adding administrators and accounts as users of the databases etc. and given the accounts all the permissions I can dream up.
There are no interesting messages in the event viewer. The SQL agent is running.
Technet found two documents but not related to the problem.
I can run SQL Analyzer and run "select * from table_name". That works on the Northwind and customer database tables - every time.
Colleagues with other installations do not get the error, and their systems return the rows correctly.
If any of you can help, I'd really appreciate it.
View 3 Replies
View Related
Jan 29, 2008
I just go a new job where I work with Point of Sale machines and I'm having a hard time figuring SQL 2005 out. When I was in school we were taught using SQL 2000. Some of our POS (Point of Sale) machines are moving over to using SQL 2005 Express and I am having the hardest time figuring all this out. All be it I haven't use SQL in a good while but I still remember how to administer SQL 2000 with Enterprise manager. Am I missing something or what's the deal hear.
Any help would be much appreciated!
Best regards,
--Bob
Bob McQuay
ADI Business Solutions
bobm@adionline.com
Network Technician
View 2 Replies
View Related
Feb 21, 2006
Hi
I had DTS the Northwind sample database from SqlServer 2000 to 2005. It's went ok and no errors. Then, I created a SP named upGetCustomer, bascially it queries the Customers table and list some of it's fields and order by CustomerId,CustomerName, Country decrementally. SP is so simple and has no errors.
Then, I created a SqlServer project in VS2005 using C#. Add store procedure class as below,
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Customers(string customerid)
{
using (SqlConnection sqlConn =
new SqlConnection("context connection=true"))
{
sqlConn.Open();
SqlPipe sqlPipe = SqlContext.Pipe;
SqlParameter param = new SqlParameter("@custId", SqlDbType.VarChar, 5);
param.Value = customerid;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "upGetCustomer";
sqlCmd.Parameters.Add(param);
SqlDataReader rdr = sqlCmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
}
}
I had taken method of created SQLCLR from source in Microsoft website, but coding is mine, and hopes it is correct. I used SqlConnection string as 'context connection=true' which I still not quite sure what does it means, hopes it mean current connection I am using on my Windows authentication.
The project did compiled & deployed OK on the VS2005 side.
The problem is that when I tried to locate the assembly on the Sql Server 2005, but can't find it anywhere on Sql Server.
I did try complied and deloyed again, it keeps saying there is an error in deployment as customers assembly is already exist on the database. I then, tried to remove this assembly on the database using SQL script, drop assembly customers in the Northwind database but got error saying it does not exist. So where is it???
Please help...
Thank you
View 1 Replies
View Related
Dec 27, 2006
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies
View Related
Mar 16, 2007
I guess I just don't get the reasoning behind the new SqlDataSource control. Haven't we just spent the last decade or so evangelizing and learning how and why to separate business logic from the display in VB 6 and VS.NET? In this age of programming for disparate devices (desktop, mobile, PocketPC, etc.) when this separation makes more sense than ever, why is MS pushing us to go back to putting our logic and data access rules and objects back in the display? It doesn't make sense. Why would anyone do this? And why would all the experts and MVPs at ASP.NET, DevX, 4GuysFromRolla, etc., go along with this?
View 4 Replies
View Related
Dec 26, 2005
What happened to nice SqlDataAdapter.Fill method in ASP.net 2.0 + How can i now access and traverse trhough table.
ex : sqlDataAdapter.Fill(dataSetname.tblsname);
dataSet11.rows[][]
View 5 Replies
View Related
Nov 7, 2006
Anyone know what happened to the release of Donald Farmer's book "The Rational Guide to Extending SSIS 2005 with Script"? I ordered it back in June and it still says "Item is not released - expected to ship July 3"
Looks like the latest update has it coming out in December... every 3 months or so the release date gets pushed back.
LOL with my luck by the time it gets released I will be finished with the giant data warehouse ETL project I've been working on since July and I will read the book and swear a lot becuase I'll find there was much easier ways to do things via script than the way I did them. :)
View 3 Replies
View Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
Jun 11, 2015
I have a transaction table to store all the transaction happened on a single day. as per my requirement I wrote the query like this select Currency Code,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where TransactionCode in ('BNT' ,'BCN','BTC','STC','SCN','SNT') group by TransactionCode,CurrencyCode,TransactionAmount order by CurrencyCode..I got the result like this
My I want to show this result like this
ARS
BNT 0 0
BCN 0 0
SCN 1 12
BTC 0 0
STC 0 0
SNT 0 0
[code]...
and so on for all the the currency lists.how can I achieve like this .
View 6 Replies
View Related
Jul 26, 2005
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies
View Related
Jun 12, 2008
I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
View 5 Replies
View Related
Aug 27, 2001
Hi, there,
I use VB to make an dll file and register on my local PC, then use sp_OACreate to create OLE object on local SQL server, it works fine. But when I register that dll on other SQL server, it dosen't work, anybody have idea about this? Thank you.
Tony
View 1 Replies
View Related
Jul 17, 2000
I see these type of objects in Books on Online. I was just wondering
if anyone knows how to use these objects, seems to have lot of good
information. Could these objects be called in VB?
--------------------------------
CreateDate Property (SQL-DMO)
The CreateDate property indicates the date and time the referenced SQLServer object was created.
Applies To
Database Object Table Object
DBObject Object TransactionLog Object
Default Object Trigger Object
Rule Object View Object
StoredProcedure Object
Syntax
object.CreateDate
Part Description
object Expression that evaluates to an object in the Applies To list
Remarks
The string returned is formatted by using the locale setting of the workstation.
Data Type
String
Modifiable
Read-only
Prototype (C/C++)
HRESULT GetCreateDate(SQLDMO_LPBSTR pRetVal);
--------------------------------------------------------
View 1 Replies
View Related
Sep 21, 2007
Anyone know how to grant a user to see all data in sys.objects view?
Thanks.
View 1 Replies
View Related
