This powershell script assists in applying a similar change on every SQL Server Instance. Or a subset. For example, adding a new generic logon account, or collecting regular information. I’ve written plenty of powershell scripts – that are customised for a particular dataset – such...
In order to know the SQL Server and Edition details you would be possibly looking at the SQL Server properties and find out the details. Occasionally it might be needed to check the same as part of a job / or a script, you need to query the SQL Server for this info. In order to do this you can use @...
Some people suggest usage of information_schema.routines View to view the definition of the procedure The problem is the datatype of that column is nvarchar(4000) and there is a chance that the definition gets truncated. So, the following wont give you full text of the definition select routine_definition...
In this post script-out-procedures-and-functions-part-2 , I showed how to script out the procedures in a single file Here is another way to do the same but different file for each procedure. The file name will be the name of the procedure. declare @sps table(proc_name varchar(100),texts varchar(8000...
One of the new features available in SQL Server 2008 is scripting the tables along with data (in the form of INSERT statements). Here are the useful links that can help you generating script with data SQL Server 2008 http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx...
This time its all about a script a backup script.Today early morning i asked to get BackUp details for some of DB’s we have, usually they ask me about the last BackUp taken so its easy for me to tell him. But this time he needs all the history of BackUp’s for some reason.So i know that iformation...
Part 1 uses information_Schema.routines view and this is based on sp_helptext declare @sps table(texts varchar(8000)) insert into @sps select 'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn') order by xtype,name create table scripts(sps...
This procedure will generate the script of a table Create Procedure GenerateScript ( @tableName varchar(100) ) as If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName) Begin declare @sql varchar(8000) declare @table varchar(100) declare @cols table (datatype varchar(50)) insert...