Generate SP or function definition in SQL Server 2005/2008

Well, today I would like to share one very quick and useful simple TSQL which can help you to generate script (definition) of stored procedure, trigger, functions etc. Generally you can do it with SSMS easily by right click on object and click on option like “Script Stored Procedure as->Create to->File”  or  “Script function as->Create to->File” etc. but if you want to generate script for many object at a time, there is one very small TSQL can come to your help which I myself used to use so many time.
Look at the TSQL below:
select so.name as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on so.id=sc.id
–P for Stored Procedure
–FN for scalar function
–IF for Inlined table function
–TF for Table function
–TR for trigger
where so.type in (‘P’,‘FN’,‘IF’,‘TF’,‘TR’)
–if you want defination of specific function or SP, include below condition too
–and so.name=’YourSPorFunctionName’
Order by so.name,sc.colid
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

ERROR FIX: Alter failed for Login sa – Cannot set a credential for principal ‘sa’. (Microsoft SQL Server, Error: 15535)

Generally It is good practice to disable SA login in SQL Server and use SysAdmin account which is created by you to secure you SQL Server from few threats. Well, this is not a scope of this article to explain why you should disable it but I would like to share one quick tip when you face above error while enabling you SA account back.
If you see error like:
ERROR FIX: Alter failed for Login sa – Cannot set a credential for principal ‘sa’. (Microsoft SQL Server, Error: 15535)
While enabling your SA account, you have to enable one check box “Map to credential” from “General” table. Once you are done with that, go to “Status” table and make your user “Enable” with radio button under “Status” tab.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

TimeStamp datatype in SQL Server

Well TimeStamp datatype is one of the old datatype in SQL Server and going to deprecated in newer version so it is not advisable to use it. The main intention to write this article is, recently I have see few people discussing TimeStamp datatype with misconception in one of the forum.
I wonder people got this datatype wrong by understanding it as a real data or time datatype. Actually TimeStamp datatype has nothing to do with Date or time in SQL Server.
As per MSDN, TimeStamp is
timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.”
Let us see one small example which proves above statement.
create table TimeStampTesting
(
Name varchar(10),
TS TimeStamp
)
Insert Into TimeStampTesting(Name)
Select ‘Ritesh’ union all
Select ‘Rajan’ union all
Select ‘Bihag’
GO
–since we are making order by on TS
–Bihag should be first as that record was inserted last
Select * from TimeStampTesting order by TS desc
Go
Update TimeStampTesting set Name=‘Rajan S.’ where Name=‘Rajan’
GO
–if you observe, this time Bihag wouldn’t first
–but Rajan S. would be the first as it updated last
–so TS is a binary unique number which updates itself automatically
–for new upate and/or insert
Select * from TimeStampTesting order by TS desc
Go
BTW, now a day, you should use RowVersion datatype rather than TimeStamp as I told you above too that TimeStamp will be deprecated and RowVersion is synonyms for TimeStamp. For more information, look at the below URL:
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com