You could use
sp_helpdb [ [ @dbname= ] 'name' ]
My Online Notepad
You could use
sp_helpdb [ [ @dbname= ] 'name' ]
exec msdb..sp_enum_dtspackages
‘ Rename File from Connection
Option Explicit
Function Main()
Dim oFSO
Dim sFileName
sFilename = DTSGlobalVariables.Parent.Connections(“Text File (Source)”).DataSource
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
‘ Check for file and return appropriate result
If oFSO.FileExists(sFilename) Then
‘Main = DTSTaskExecResult_Success
Main = DTSStepScriptResult_ExecuteTask
Else
‘Main = DTSTaskExecResult_Failure
Main = DTSStepScriptResult_DontExecuteTask
End If
Set oFSO = Nothing
End Function
‘ Rename File from Connection
Option Explicit
Function Main()
Dim oPkg
Dim oConn
Dim oFSO
Dim oFile
Dim sFilename
Dim sFileDestination
Dim sMonth
Dim sDay
Dim sYear
Dim sHour
Dim sMinute
Dim sSecond
‘ Get reference to the current Package object
Set oPkg = DTSGlobalVariables.Parent
‘ Get reference to the named connection
Set oConn = oPkg.Connections(“Text File (Source)”)
‘ Get the filename from the connection
sFilename = oConn.DataSource
sMonth = CSTR( MONTH(Now()))
sDay = CSTR(DAY(Now()))
sYear = CSTR(YEAR(Now()))
sHour = CSTR(HOUR(Now()))
sMinute = CSTR(MINUTE(Now()))
sSecond = CSTR(SECOND(Now()))
sFileDestination = DTSGlobalVariables(“dts_ArchiveLocation”).Value & DTSGlobalVariables(“dts_FileName”)
‘MsgBox sFileDestination & sMonth & sDay & sYear & “.TXT”
‘ Rename the file
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
‘oFSO.MoveFile sFilename, sFilename & “.bak”
oFSO.MoveFile sFilename, sFileDestination & sYear & sMonth & sDay & “_” & sHour & sMinute & sSecond & “.TXT”
‘ Clean Up
Set oConn = Nothing
Set oPkg = Nothing
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
sp_help_job returns information about jobs that are used by SQLServerAgent service to perform automated activities in SQL Server.
EXEC MSDB.DBO.SP_HELP_JOB
List all the database on SQL Servers.
SQL SERVER 2005 System Procedures
EXEC sp_databases
EXEC sp_helpdb
SQL 2000 Method still works in SQL Server 2005
SELECT name FROM sys.databases
SELECT name FROM sys.sysdatabases
The db_datareader role
The db_datareader role has the ability to run a SELECT statement against any table or view in the database. This role is often used in reporting databases where users would be coming in via a third-party reporting tool and building their own ad-hoc queries. If you need to restrict a user to only be able to read from certain tables, the db_datareader is not the right choice as it would have to be combined with the explicit use of DENY permissions on tables the user shouldn’t be able to access. A better practice would be to create a user-defined database role with the proper permissions.
One key point about the db_datareader role is that it always has the right to read all tables and views. That means if you create a new table in the database, a member of the db_datareader role has access immediately. This differs greatly from a user-defined role with which you must explicitly grant each permission. Therefore, unless you give a user-defined role permission to access a database object such as a table or view, that role can’t do so. Not only does it have access to user tables but also system tables. That means a member of the db_datareader role can execute a SELECT query against a system table even you decide to revoke public access to SELECT against these tables (keep in mind that revoking default permissions would result in an unsupported configuration so far as Microsoft is concerned).
declare @DateP char(10)
select @datep=107277 — ALSO OK FOR 99 YEARS
select
dateadd(dd,@datep-(@datep/1000)*1000-1, convert(datetime,’01/01/’+ right(convert(varchar,@datep/1000),2))) as Date_ENT
SELECT CONVERT(VARCHAR(8), GETDATE()-365, 112)
This is a way to query and get the date in the following format:
YYYYMMDD
I came across some dates saved in the database in this format as a varchar and had to use this to initially get the date and then convert it from and integer to varchar.