The Script below creates a Stored Procedure that
returns a Macro SQL Statement. When this SQL statement is executed, it inturn returns BCP Out Statements for all the
tables in a given database. The database name should be passed as a parameter to the procedure.
Before the procedure is created, the SA Password must be changed to your Database SA Password, for the BCP statements to work correctly.
IF object_id('sp_bcp_scripts') >99999
drop procedure sp_bcp_scripts
go
create procedure sp_bcp_scripts (@dbname varchar(50))AS
BEGIN
/* The Script below creates a Stored Procedure that
returns a Macro SQL Statement which when executed
will inturn return BCP Out Statements of all the
tables in a given database passed as a parameter
Author : Seshu Kanuri, DBA, EB2B Commerce, Inc.
*/
declare @sqlstr varchar(4000)
select @sqlstr="select 'bcp '+" +"'"+ @dbname +"..'"+ '+name+'+ "' OUT prod_Db/DataIn/'"+'+name+'+ "'.dat -c -e prod_Db/DataOut/' " + '+name+'+" '.err -S Prod1 -U sa -P oasis -t ¢' from sysobjects where type='U' order by name "
select @sqlstr
/* To run the script - Execute sp_bcp_scripts Northwind */end
go 
Modification of DR_Script_Logins.sql by Bruce Canady.Puts out a script to add new logins or change the password of existing logins.I insert the output into a table in my production-db. insert database.dbo.login_table exec master.dbo.dr_script_loginsThat way its backed up with the data, and the logins can be restored anywhere by the script i added at the […]
2004-09-03 (first published: 2004-05-26)
2,521 reads
How about getting the data when the table data is first queried? The following script checks to see if the specified table (@MyTable) has been updated today. If so, just return the data found. If not, then get the data from a "fresher source". Great for ASP that performs table reporting and needs "baked daily" […]
2002-04-23
1,554 reads
2007-04-16 (first published: 2007-03-20)
14,078 reads
This function returns the SQL Build Number as an int. i.e. 7, 8, or 9. @@Version is great, but super long, and the formatting of the string has changed over the years. This helps me find out what version of SQL I'm working on in a quick and efficient manner. The script includes the drop […]
2006-08-08 (first published: 2006-06-23)
1,229 reads
This function returns the SQL Build Number as a nvarchar(20) string. i.e. '8.00.760' @@Version is great, but super long, and the formatting of the string has changed over the years. This helps me find out what build of SQL I'm working on in a quick and efficient manner. The script includes the drop statement as […]
2006-08-09 (first published: 2006-06-23)
1,017 reads