How to get list of all stored procedures?

You can use following stored procedure with four optional input parameters to get a list of all stored procedures in the current environment.

EXEC SP_STORED_PROCEDURES @sp_name = 'procedure name'
, @sp_owner = 'schema name'
, @sp_qualifier = 'database name'
, @fUsePattern = 'fUsePattern'

Note:

1. All parameters are optional.
2. @sp_name and @sp_owner support wildcard pattern matching (underscore “_“, percent “%” and brackets []).
3. @sp_qualifier it will have null or current database name only.
4. @fUsePattern, it can be 0 (wildcard pattern matching is off) or 1 (wildcard pattern matching is on), by default it is 1.

Examples:

1. To see complete list of all stored procedures in current database
   EXEC SP_STORED_PROCEDURES
2. To see complete list of procedures, procedure name starting with “fn” characters
   EXEC SP_STORED_PROCEDURES @sp_name = 'fn%'
3. To see complete list of procedure, procedure name starting with “fn” and schema name starting with “s” characters
   EXEC SP_STORED_PROCEDURES @sp_name = 'fn%', @sp_owner = 's%'

Moving database files to another new location

You can move database data and log files to any other location by following the below steps, these come under Planned Relocation.

1. Check if there is any user connected to DB by executing the below query.
   
   SELECT SPID,LOGINAME,HOSTNAME,PROGRAM_NAME FROM SYS.SYSPROCESSES WHERE DBID=DB_ID('database_name')
   
   If there is any replication agent [Log Reader Agent] is running, stop that agent from Replication Monitor, Kill all other user connection if they are not important as:
   
   KILL @SPID – get spid from above query
   
   
2. Run ALTER DATABASE database_name SET OFFLINE.
3. Move the file to the new location.
4. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name' )
5. Run ALTER DATABASE database_name SET ONLINE.
6. Run replication agent [Log Reader Agent], if stopped.
   

Example:

ALTER DATABASE DB1 SET OFFLINE

Copy log file from the current location to a new location by using Copy and Paste. E.g. you move File1.ldf from c drive to d drive.

ALTER DATABASE database_name MODIFY FILE ( NAME = 'DB1_Log', FILENAME = 'D:\File1.ldf' )

ALTER DATABASE DB1 SET ONLINE

Table's rows count without using COUNT() function

You can get table's total number of rows as:

SELECT SUM(ROWS) AS Total_Rows
FROM SYS.SYSINDEXES
WHERE ID=OBJECT_ID('Table1')
AND INDID IN (0,1)

OBJECT_ID('Table1'):
This function will return the object id for table "Table1" or any other specified table.

INDID:
It can be 0 for heap and 1 for clustered index, and it will have only one value at a time 0 or 1, greater than 1 values are for non-clustered indexes.