How to get Column Names of All Tables in a database through SQL Query / SysTables in SQL Server 2008

How to list all tables and each table, loop through each column using SQL query

It is not uncommon to get the list of all column names from all available tables; at times some valuable information peeps out when one researches it. The following query will get you exactly that


select sys.syscolumns.name as ColumnName, sys.sysobjects.name as TableName from sys.syscolumns,sys.sysobjects where sys.sysobjects.id = sys.syscolumns.id

How to Split SQL String to Multiple String using Delimiter

How to check if a text exists within another in SQL Server 2008 / How to split SQL Column based on Comma

You can use the combination of CharIndex and Substring function in SQL to split the string based on delimiter.

Here is a way to split a column based on Comma

Select LTRIM(SUBSTRING(EmpAddress, CHARINDEX( ',',EmpAddress)+1,len(EmpAddress))) from #TempEmployee


We have used Ltrim to remove any leading spaces

A RETURN statement with a return value cannot be used in this context. - Create Function

If this error occurs in the Create Function that uses a table then the variable after return statement should be removed

The syntax should be like

Create Function
Returns Table
(
Table Definition here
..
..
)
as
BEGIN
..
..
RETURN -- note no value is returned here
END

How to get the list of Functions in Database

How to Filter Functions from Sys.Objects

The following query will list of the Functions that are part of the database:

SELECT * FROM

sys.objects

where type in ('FN', 'IF', 'TF')

Cannot alter column 'X' because it is 'timestamp'.

How to Change DataType of TimeStamp Column.

Datatype of Timestamp column cannot be deleted. Hence it is advised to drop the column and re-create it

For example,

ALTER TABLE [dbo].[PerformanceMaster] DROP COLUMN PerformanceDate
ALTER TABLE [dbo].[PerformanceMaster] ADD PerformanceDate datetime2

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

TimeStamp Column in SQL Server

TimeStamp is used to store unique binary numbers within a database. This column is autogenerated with a storage size of 8 bytes.

SQL Server - TimeStamp Column

IF you want to store the Date and Time (e.g., updated time etc) use datetime2 datatype instead of timestamp.

SQL Server DateTime Column to Record Date and Time

The error "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." occurs when you try to insert the value using Insert statement. Leaving the column in the Insert will automatically update the value

Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, PerformanceDate     )
values (@PerformanceID, @EventID, @ArtistID, @UpdateDate )


Should throw and error. Use the following instead:


Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, )
values (@PerformanceID, @EventID, @ArtistID)

Information_Schema.Routines doesn't return all procedure names

How to get a list of Procedures containing Specific Text / How to get a list of Procedures using Particular Table



Here is one method we generally use for returning Stored Procedures containing a particular text

SELECT ROUTINE_NAME



FROM INFORMATION_SCHEMA.ROUTINES


WHERE ROUTINE_DEFINITION LIKE '%used_by_id%'
AND ROUTINE_TYPE = 'PROCEDURE'


order by ROUTINE_NAME

 
However, we found it has some problems when the text appears at the fag end of big procedures. Instead try the following also and match the result
 
SELECT OBJECT_NAME(id)




FROM syscomments



WHERE [text] LIKE '%used_by_id%'



AND OBJECTPROPERTY(id, 'IsProcedure') = 1



GROUP BY OBJECT_NAME(id)







SELECT Name



FROM sys.procedures



WHERE OBJECT_DEFINITION(object_id) LIKE '%used_by_id%'



order by name







SELECT OBJECT_NAME(object_id)



FROM sys.sql_modules



WHERE Definition LIKE '%used_by_id%'



AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1



order by OBJECT_NAME(object_id)