Q1. How to find the list of all the Table Name form the given database ?
Ans:- With the help of below given query we can find out the list of all the table name from the given database.
USE IPISMIS -- (Your Database name. Here "IPISMIS " is my database name)
SELECT Name FROM sys.tables
Q2. How to find the list of all the Procedure Name form the given database ?
Ans:- With the help of below given query we can find out the list of all the table name from the given database.
USE IPISMIS -- (Your Database name. Here "IPISMIS " is my database name)
SELECT Name FROM sys.procedures
Q3. System Table in Sql Server, List some name of the System Tables.
Ans:- SQL Server maintains a set of tables that contain information about all the objects, data types, constraints, configuration options, and resources available to the SQL Server. This set of tables is called the system Table.
One subset of tables exists only in the Master database and contains systemwide information.
Another subset of tables exists in every database (including Master) and contains information about the objects and resources belonging to that database.
System Tables in the Master Database :- These tables store server-level system information.
sysaltfiles ,syscacheobjects,syscharsets,sysconfigures,syslockinfo,syslogins etc.
System Tables in Every Database :- These tables store database-level system information for each database.
syscolumns,syscomments,sysconstraints,sysdepends,sysfiles etc.
Q4. How to find the Structure of the given table .
Ans:- For find out the Structure of the table (All the information of table and its column) we have to way either we use "sp_help" system procedure or we use
INFORMATION_SCHEMA table. example is given below
First Way -
sp_help EmpTest ---(here EmpTest is our table name)
Second Way -
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME ='EmpTest' ---(here EmpTest is our table name)
Ans:- With the help of below given query we can find out the list of all the table name from the given database.
USE IPISMIS -- (Your Database name. Here "IPISMIS " is my database name)
SELECT Name FROM sys.tables
Q2. How to find the list of all the Procedure Name form the given database ?
Ans:- With the help of below given query we can find out the list of all the table name from the given database.
USE IPISMIS -- (Your Database name. Here "IPISMIS " is my database name)
SELECT Name FROM sys.procedures
Q3. System Table in Sql Server, List some name of the System Tables.
Ans:- SQL Server maintains a set of tables that contain information about all the objects, data types, constraints, configuration options, and resources available to the SQL Server. This set of tables is called the system Table.
One subset of tables exists only in the Master database and contains systemwide information.
Another subset of tables exists in every database (including Master) and contains information about the objects and resources belonging to that database.
System Tables in the Master Database :- These tables store server-level system information.
sysaltfiles ,syscacheobjects,syscharsets,sysconfigures,syslockinfo,syslogins etc.
System Tables in Every Database :- These tables store database-level system information for each database.
syscolumns,syscomments,sysconstraints,sysdepends,sysfiles etc.
SQL Server Agent Tables in the msdb Database :- These tables store information used by SQL Server Agent.
Tables in the msdb Database:-These tables store information used by database backup and restore operations.
Ans:- For find out the Structure of the table (All the information of table and its column) we have to way either we use "sp_help" system procedure or we use
INFORMATION_SCHEMA table. example is given below
First Way -
sp_help EmpTest ---(here EmpTest is our table name)
Second Way -
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME ='EmpTest' ---(here EmpTest is our table name)
Q5. How to find a Text in all the Stored Procedure from the given database .
Ans:- This situation will be arise when you delete some column name or rename column name and want to change or remove from the store procedure in which this column name were used and you have 1000 of Store procedure then how you find the name of the procedure in which you use this column name. So, we may find out form the below given query.
Use IPISMIS --(Here IPISMIS is my database name you use your data base name.)
SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Product%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Name%'
Ans:- This situation will be arise when you delete some column name or rename column name and want to change or remove from the store procedure in which this column name were used and you have 1000 of Store procedure then how you find the name of the procedure in which you use this column name. So, we may find out form the below given query.
Use IPISMIS --(Here IPISMIS is my database name you use your data base name.)
SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Product%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%Name%'
here we want the all procedure name in which we use product and Name column or text.
No comments:
Post a Comment