In this post ,we will generate the script that will return us all the table names with column names and data type of those columns. We often need this information. Let's say we are going to prepare mapping document for load the data from Source Database to Destination database. We can get the list of all tables with column names and data types from Source Database and Destination Database and then paste in Excel and map the required input columns to output columns for ETL Process.
SELECT T.Name AS TableName,
Schema_name(T.schema_id) AS SchemaName,
C.Name AS ColumnName,
Ty.Name AS ColumnDataType,
C.is_nullable AS IsNullAble,
C.is_identity AS IsIdentity
FROM sys.tables T
INNER JOIN sys.columns C
ON T.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.types Ty
ON C.system_type_id = Ty.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.name
SELECT T.Name AS TableName,
Schema_name(T.schema_id) AS SchemaName,
C.Name AS ColumnName,
Ty.Name AS ColumnDataType,
C.is_nullable AS IsNullAble,
C.is_identity AS IsIdentity
FROM sys.tables T
INNER JOIN sys.columns C
ON T.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.types Ty
ON C.system_type_id = Ty.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.name
How to find String in All Stored Procedures in SQL Server Database
We often have the situations where we want to find string in all the stored procedures in a database. Some of the situations are listed below
System.sql_modules and sys.syscomments system views can be used to dig into the definition of objects and search if any other object is used in the definition or not. Let's suppose if I am looking for comments in objects Creator Name=Aamir. I can use both system views to find out required information by using below queries.
SELECT OBJECT_NAME(OBJECT_ID) AS ObjectName,
definition AS ObjectDefinition
FROM sys.sql_modules
WHERE definition LIKE '%Creator Name=Aamir%'
SELECT OBJECT_NAME(id) AS ObjectName,
TEXT AS ObjectDefinition
FROM sys.syscomments
WHERE TEXT LIKE '%Creator Name=Aamir%'
- Find out how many stored procedure are using Cross Database Queries, so we want to search for Database name in all the stored procedures in a database.
- Find out in how many stored procedure a table/view is used in a Database? This can be situation where we are planning to change the name of table/view and want to make sure if any Stored Procedure/s will be effected.
- Find out a column name used in Stored Procedure/s in SQL Server Database.
- Find out some comments in all the Stored Procedure to see who created those Stored Procedure, if Creator name is used in the comments.
System.sql_modules and sys.syscomments system views can be used to dig into the definition of objects and search if any other object is used in the definition or not. Let's suppose if I am looking for comments in objects Creator Name=Aamir. I can use both system views to find out required information by using below queries.
SELECT OBJECT_NAME(OBJECT_ID) AS ObjectName,
definition AS ObjectDefinition
FROM sys.sql_modules
WHERE definition LIKE '%Creator Name=Aamir%'
SELECT OBJECT_NAME(id) AS ObjectName,
TEXT AS ObjectDefinition
FROM sys.syscomments
WHERE TEXT LIKE '%Creator Name=Aamir%'
No comments:
Post a Comment