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.
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.
-- 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)
/****************************************************************************/ /* 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
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
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......
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?
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..
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.
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 ?
"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..."
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
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?
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?
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
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.
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
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 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
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?
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[][]
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. :)
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"
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
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
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)
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.
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.
